locked
How steps for convert an old adp file to accdb in Access 2010/2013 RRS feed

  • Question

  • I have a client with an old adp file they are running in Access 2010.  Some of their users have moved to Access 2013 and the adp format is no longer supported in that version of access.  I have tried searching the web, but I have not found a definitive set of instructions on how to migrate the content of this adp file to the newer accdb format.  I have created a new .accdb went to External data, Import, Access and pulled over all the objects found in that ui.  It pulled in tables as well, but it was my understanding with the adp format all of the tables were in sql server, not access.

    Any help would be greatly appreciated to help me figure out how to migrate this content and get rid of the adp file as I am admittedly not very knowledgeable of this adp file format as well!

    Thursday, March 26, 2015 1:33 PM

All replies

  • Correct. ADPs support SQL Server directly.

    Cause this a discontinued feature, you cannot upsize the ADP to 2013 ACCDB without problems.

    The easiest way would be installing the Access 2010 runtime in parallel. But I'm not a friend of parallel installed Office versions.

    The other approach would be a real migration to ACCDB. But you need to link the necessary tables and views from SQL Server as tables in Access. Then you need to carfully test each form, report, query.. and adapt surely also some code.

    Thursday, March 26, 2015 1:43 PM
  • Thanks for the help Stefan.  I was actually able to do this pretty easily by doing a couple of things:

    1. Created a blank new Access DB (MyAccessDB_new_format.accdb)
    2. Opened it and went to the External Data menu, then imported all the objects from the old Access file (MyAccessDB.adp)
    3. Went to View code to see the VBA code and then added the reference to the ADODB library for the code.
    4. Created a new ODBC connection on the PC to the local SQL Server database (called MySQLServerAppDB).
    5. Deleted all the local tables that got imported in the Access db.
    6. Went to the External Data menu, then added all the db objects as linked tables (All the tables now in the access db with the globe icon next to them) and renamed them in Access from default naming schema of "dbo_TableName" to just "TableName".
    7. Tested it and was getting some weird dbo message, so looked at the VBA code and all the queries had schema qualification (i.e.:  select * from dbo.table1), so I did a find replace and got rid of "dbo." (found 10 occurrences) and changed any form or report references in their property sheets with dbo in the name and removed "dbo."
    8. Tested it again and then got some weird unique table error message.  I just commented this line out and it works fine in my app.  

    Hope that helps someone!

    Sunday, March 29, 2015 1:50 PM
  • Jason

    So far this has helped me out a lot.  Would you mind telling me with version of the ADODB library you made a reference to?  was it 6.1 or a legacy dll?  thank you for your time

    Wednesday, October 21, 2015 2:34 AM
  • @Nathan187, I am in the middle of converting several .adp databases to the  ACCDB format. Here is a screenshot of the references I am using that works for me. Hope it helps. 

    Sunday, November 1, 2015 12:04 AM
  • Thank you again.  I'm not sure if you're still following this thread.

    I have a .accdb profile that opens fine in Access 2013.  The problem is...I just want to change the Data Link Properties.  On a regular .adp file in Access 2010...it's easy.  I can add the connection command to the quick access toolbar and it's enabled.  However, opening the .accdb in Access 2010 or Access 2013...the connection command in the quick access toolbar is grayed out.  And of course, there's no adequate documentation on how to solve this or get this enabled. 

    Would you happen to have any ideas?

    Monday, February 1, 2016 12:59 PM
  • Instead of importing the tables, you should be linking to them. Do the same with your views. Look at the ODBC wizard on the External Data tab.

    Depending on the complexity of your stored procedures, you might have to do some converting to VBA or use pass-through queries. There is no real "big" answer to this. Mostly, seeing what you have and what isn't working.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 1, 2016 4:15 PM
  • Thanks for responding. 

    The adp file/project...doesn't use a ODBC connection.  It's using the connection referenced in the Data Link Properties. 

    Wednesday, February 3, 2016 7:53 PM
  • Thanks for responding. 

    The adp file/project...doesn't use a ODBC connection.  It's using the connection referenced in the Data Link Properties. 

    Of course the ADP is not using an ODBC connection. But your NEW non-ADP database does use one at least initially. Later, you can change over to a DNS-less connection string.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, February 3, 2016 9:23 PM
  • thank you for the quick reply..no sarcasm

    is there a doc or something that tells you how to do it?  i'm kind of lost on this one.  my new non-adp database is a accdb ...  so if i create a dns....where do i change the connection string

    thank you

    Thursday, February 4, 2016 12:29 AM
  • sorry to keep bugging you with this but I am still trying to get this to work.  i'm starting to feel it's not possible to set the connection (data link property) on an accdb database/file.

    i created a dummy connection to a sql server via dsn/odbc....but still can't change the connection

    not sure what i'm doing wrong

    Sunday, February 7, 2016 11:08 PM
  • You are going about this wrong. With an ADP you use a data link. But with an ACCDB you use an ODBC connection. It's like linking to another database's tables. Click on the External data tab. Select the ODBC icon and follow the wizard to create a connection to the SQL server instance (link NOT import).

    Create a new DSN. Put in the name of the SQL Server instance.

    As you go through the steps change the default database from master to your SQL database. That will give you a list of your tables and views. Select them and that will set up linked tables.

    I'd show you pictures but I'm at home and don't have any SQL servers here.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Sunday, February 7, 2016 11:19 PM
  • thanks for the quick reply (no sarcasm)

    i think the issue is....the accdb file i have...comes from an adp file.  the adp file has the the data link property defined.  the adp file has forms, reports, class modules, etc....that rely on the currentproject.connection property.  in the adp file, i can change that (data link property).  in the blank accdb file, i can import the forms, reports, modules from the adp file.   but in the accdb file, i can't change that connection property.  i can create an odbc connection, etc. 

    i guess i'm not explaining it correctly

    Monday, February 8, 2016 1:33 AM
  • You should be establishing new links to the back end. I don't understand why you can't change the conenction property for each table. If you use the link manager there is a checkbox in the lower left corner to prompt for a new location...at least that is how it is in every version of Access I've used up to 2010.

    Maybe I'm just not understanding your situation. Can you create a new accdb and link the tables?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 8, 2016 3:11 PM
  • Hey Bill, thanks for helping me and your quick response (no sarcasm)

    Ok, while I think there is no solution, I was able to ascertain some more information that I think is helpful.

    The original /existing ADP access file is working fine.  There is one thing I did notice when I really paid attention to the tables.  A lot of the queries in this ADP file are stored procedures and functions -- from the SQL server.  Whoever created this database, I don't know how they were able to "link" to stored procedures and functions as queries.  When I try to create such links, I can only link tables...not stored procedures, views or functions.  I also learned that the database it originally was linking to was a SQL 2005 database.  We recently migrated that database to newer version of SQL.  In the data link properties of the ADP file, I was able to change the name of the server and the ADP database works fine after the switch.

    So now it's starting to make sense.  For example, on one of the forms, the drop down (combo box) is bound to a stored procedure.  Since I can't import or link to that stored procedure on the server, then of course it won't work in my accdb (i tried creating DSN with an account as dbo owner but still can't link them all).  If I try to export one of the 'queries' from the original / existing ADP file into a accdb project database, it will say 'Queries' cannot be imported, exported, or copied to Access database files

    I don't think I have ever seen this:  linking to stored procedure or functions as access queries.  When I right click on one of the stored procedures and try to go to design view, I get a message saying "This version of '' does not support design changes with the version of Microsoft SQL server to which your Access project is connected.  See Microsoft Update Web site"...then a subsequent message saying pretty much the same thing.

    Not sure if this is helpful but hopefully I'm closer to something.

    Monday, February 8, 2016 6:12 PM
  • You can only link to tables and views. When I create a SQL back end I prefix my tables with "tbl" and my views with "v". That keeps them separate in the list to link to. Things like stored procedures and functions will need to be translated to Access and stored locally. You can't change the connection on those because they really don't exist in an accdb.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 8, 2016 7:30 PM
  • Thanks for the reply.  Last question...then how were they able to do that?  Am I'm going to create all these by hand?  in ADP files, they were able to link to stored procs, etc?  Did they use a 3rd party tool.  I find it interesting that it is suffixed with (dbo)
    Tuesday, February 9, 2016 12:40 AM
  • Thanks for the reply.  Last question...then how were they able to do that?  Am I'm going to create all these by hand?  in ADP files, they were able to link to stored procs, etc?  Did they use a 3rd party tool.  I find it interesting that it is suffixed with (dbo)

    Create by hand? Yes. As to the dbo_ in the table name you can rename the table in Access and drop the dbo_ part. I run a function that does that for me. Be sure to add a reference to the library named "ADO ext. x.x for DDL and Security"

    Public Function dev_TbrRenameDBOTables()
    'Purpose  : Remove SQL Server "dbo_" from all table names.
    'DateTime : 10/28/2000 15:17
    'Author   : Bill Mosca
        Dim cnn As ADODB.Connection
        Dim cat As ADOX.Catalog
        Dim tbl As ADOX.Table
        Dim strPrefix As String
    
        On Error GoTo err_RenameDBOTables
    
        strPrefix = "dbo_"
        Set cnn = CurrentProject.Connection
        Set cat = New ADOX.Catalog
        cat.ActiveConnection = cnn
        For Each tbl In cat.Tables
            If tbl.Name Like strPrefix & "*" Then
                tbl.Name = Mid(tbl.Name, Len(strPrefix) + 1)
            End If
        Next
    
        Application.RefreshDatabaseWindow
        MsgBox "Tables successfully renamed.", vbInformation, "dbo_ Dropped"
    
    exit_RenameDBOTables:
        On Error Resume Next
        Set cat = Nothing
        Set cnn = Nothing
        Exit Function
    
    err_RenameDBOTables:
        Select Case Err.Number
    
            Case Else
                MsgBox "An error (" & Err.Description & ") occurred in RenameDBOTables.", vbCritical, "Error!"
        End Select
    
        Resume exit_RenameDBOTables
    
    
    End Function


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals



    Tuesday, February 9, 2016 4:14 PM
  • @Nathan187, I am in the middle of converting .adp projects to .accdb as well. Microsoft has been warning us for a lot of years that the next version of Office wouldn't support connecting to a SQL server via an adp project. Well they finally kept their promise with 2013. It's especially bad for me because I am on a military contract and the military is pushing 2013 updates so everything breaks immediately.

    With an adp you could simply point to a database on the server and all the Tables, Views, Stored Procedures and Functions were available to use in the MS Access front end. I had used Functions almost exclusively for the data source of all my Forms and Reports because I could pass parameters to them dynamically. Converting them is going to be a real pain.

    What I did for the first one was to create a blank .accdb and exported all the Forms, Reports and Modules (if you use them) from the .adp file into the .accdb

    I created the ODBC connection for the .accdb and linked to all of the Tables and Views I had  so far used in the .adp

    I had to create all new Views by copying and pasting the Functions (Stored Procedures too) and remove the parameters.

    I then painstakingly went through every Form and Report and changed the code to use a new View. If I needed to pass a parameter I used the built in Command Button Wizard and opted for "Open the form and find specific data to display" which then lets you pass in parameters to the View to only return specific records. 

    If you are not familiar with what I am saying, it's probably time to hire a consultant. Otherwise you have a little bit of a learning curve. 

    As for the dbo.Tablename , read this post in StackOverflow (Which you should be familiar with) 

    http://stackoverflow.com/questions/1155120/sql-server-roles-schemas-users

    I don't look forward to the next conversion, it is tedious as anything I have ever done. Good Luck with it. Let me know if I can answer any other questions for you. 


    Tuesday, March 15, 2016 11:58 PM
  • AkAlan, I do not understand why SPs and Functions would have to be copied/converted to Views. Can't the SPs be retained on the server and invoked via pass-thru query?

     
    Monday, July 3, 2017 9:51 PM
  • Yes, they can.

    But I guess he means:

    Using parameters in an .adp is easier than in an .accdb with linked tables and views. Using passthrough queries works, but passing parameters requires always more effort (using DAO or ADO, materializing the result set).

    Tuesday, July 4, 2017 8:33 AM
  • As many others, I am going through this conversion too.
    I did a fair amount of Access... many years ago though!

    The adp used quite a few stored procedure calls, coded as adCmdText because it was convenient. I suppose this would convert nicely to PassThrough queries.

    Could someone give me a quick heads up as to how I should do this?

    The original code looks like this

            Set cmd = New ADODB.Command
            With cmd
                .ActiveConnection = Application.CurrentProject.Connection
                .CommandText = "EXEC dbo.p_GetUserGroups"
                .CommandType = adCmdText
            End With
            
            Dim rst As ADODB.Recordset
            Set rst = cmd.Execute
            
            Dim idx As Integer
            idx = 0
            
            While Not rst.EOF

    ...

    Thanks


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net


    • Edited by EricLeBouffon Tuesday, August 8, 2017 10:29 PM removed "noise"
    Tuesday, August 8, 2017 10:28 PM
  • Thursday, November 23, 2017 10:23 PM
  • I know this is an old thread. I could not get the above snippet to work in 2016 to remove the dbo_... I did find this snippet and it worked.

     

    referenced from this site...https://www.microsoftaccessexpert.com/Microsoft-Access-Code-RemoveDBOPrefix.aspx

    Public Sub Remove_DBO_Prefix()
    
    Dim obj As AccessObject
    Dim dbs As Object
    
        Set dbs = Application.CurrentData
    
        'Search for open AccessObject objects in AllTables collection.
       For Each obj In dbs.AllTables
            'If found, remove prefix
           If Left(obj.Name, 4) = "dbo_" Then
                DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
            End If
        Next obj
     End Sub

    Cheers...still working on getting everything working. I should have done this a while back.



    • Edited by Jim Estabrook Wednesday, January 24, 2018 9:44 PM error in text
    Wednesday, January 24, 2018 9:42 PM
  • My code might not have worked for you if you didn't add the ADO ext. x.x for DDL and Security. That library is needed for the ADOX objects. But your code does the same thing. It just uses DAO instead. I should have mentioned that.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, January 26, 2018 8:01 PM