none
Simple Task - Get table from Access 2010 into Sql Server 2008

    Question

  • I am having a difficult time getting an Access table with 71 fields and 884046 records from an accdb database file imported into SQL Server 2008 R2.  I have tried several different techniques for accomplishing this seemingly trivial task yet for some reason I have been unsuccessful.  Here is what I have tried so far:

     

    1 - Export to Excel 97-2003 (xls) from Access 2010 and then Import into SQL Server using SQL Manager

    Result: This works without errors but only 65535 records get copied to the Excel file and then subsequently imported into the SQL table.

     

    2 - Use the SQL Upsizing Wizard in Access 2010, I selected Create New Database, created a dsn to SQL Server, did not select "only create table structure", did not select to create timestamp fields or any attributes, no application changes.

    Result: Error message "Table was skipped or export failed"

     

    3 - Create an external link to an existing SQL Server table so I can execute an insert command to copy the data from the Access table to the linked SQL Table.

    Result: Cannot open database 'tablename'.  It may not be a database that your application recognizes or the file may be corrupt.

    Note I have tried this with several different tables from several different databases and get the same error.  Strangely, this technique worked yesterday on this same server but will not work today.

     

    4 - Use SSMA 2008 for Access v4.0 to attempt to migrate the data from the accdb file to SQL Server.

    Result: Get the error 'There is nothing to process' when the 'Convert, Load, and Migrate' option is selected for the Access table.

     

    I don't care which of these four techniques I use for the migration, I just need at least one of them to work.  Does anyone have any suggestions?

    Saturday, July 24, 2010 4:47 PM

Answers

  • The point 3 should have worked without any problem.  When creating the external link from Access, Access will ask you to either create a new DSN or use an existing one as it prefers create links with DSN instead of DSN-Less link.  When creating the link, you should make sure to create a System DSN instead of a User DSN.  Also, if you change the design of the sql-server table, you should refresh the link by either deleting/recreating the link or using the Database Links Manager.

    If this doesn't work, you could use a JET heterogeneous query (mix of different databases file/server, see http://support.microsoft.com/kb/200427 for more info) to transfert data from Access to SQL-Server; for example:

        Dim db As DAO.Database
        Set db = CurrentDb

        Dim sqlserver As String
        sqlserver = "ODBC;Driver={SQL Server Native Client 10.0};Server=SYLVAIN2\SQL2008;Database=Test1;Trusted_Connection=yes;"

        Dim sql As String
        sql = "Insert into [""" & sqlserver & """].TableA (a, b) Select 10, 10"

        db.Execute sql, dbFailOnError

    If this doesn't work, the final solution would be to use ADO to transfert your data but then, you'll have to have a loop and transfert each record individually.  You'll find numerous examples on the web on how to use ADO (caution: ADO, not ADO.NET) from Access VBA.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <programphases> wrote in message news:b9b5c436-80d1-40eb-b8ce-856a972a2317@communitybridge.codeplex.com...

    I am having a difficult time getting an Access table with 71 fields and 884046 records from an accdb database file imported into SQL Server 2008 R2. I have tried several different techniques for accomplishing this seemingly trivial task yet for some reason I have been unsuccessful. Here is what I have tried so far:



    1 - Export to Excel 97-2003 (xls) from Access 2010 and then Import into SQL Server using SQL Manager

    Result: This works without errors but only 65535 records get copied to the Excel file and then subsequently imported into the SQL table.



    2 - Use the SQL Upsizing Wizard in Access 2010, I selected Create New Database, created a dsn to SQL Server, did not select "only create table structure", did not select to create timestamp fields or any attributes, no application changes.

    Result: Error message "Table was skipped or export failed"



    3 - Create an external link to an existing SQL Server table so I can execute an insert command to copy the data from the Access table to the linked SQL Table.

    Result: Cannot open database 'tablename'. It may not be a database that your application recognizes or the file may be corrupt.

    Note I have tried this with several different tables from several different databases and get the same error. Strangely, this technique worked yesterday on this same server but will not work today.



    4 - Use SSMA 2008 for Access v4.0 to attempt to migrate the data from the accdb file to SQL Server.

    Result: Get the error 'There is nothing to process' when the 'Convert, Load, and Migrate' option is selected for the Access table.



    I don't care which of these four techniques I use for the migration, I just need at least one of them to work. Does anyone have any suggestions?

    Monday, July 26, 2010 12:10 AM

All replies

  • Hello,

    I created a System DSN to get connected to a SQL Server 2008 R2 database using ODBC Data Source Administrator and SQL Server Native Client, then I opened an accdb database, make a right click on a table, chose Export, and then selected “ODBC database”, provided the name of the table on the SQL Server database, chose the System DSN newly created, and finally received the message “Successfully exported”.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Sunday, July 25, 2010 4:47 AM
  • The point 3 should have worked without any problem.  When creating the external link from Access, Access will ask you to either create a new DSN or use an existing one as it prefers create links with DSN instead of DSN-Less link.  When creating the link, you should make sure to create a System DSN instead of a User DSN.  Also, if you change the design of the sql-server table, you should refresh the link by either deleting/recreating the link or using the Database Links Manager.

    If this doesn't work, you could use a JET heterogeneous query (mix of different databases file/server, see http://support.microsoft.com/kb/200427 for more info) to transfert data from Access to SQL-Server; for example:

        Dim db As DAO.Database
        Set db = CurrentDb

        Dim sqlserver As String
        sqlserver = "ODBC;Driver={SQL Server Native Client 10.0};Server=SYLVAIN2\SQL2008;Database=Test1;Trusted_Connection=yes;"

        Dim sql As String
        sql = "Insert into [""" & sqlserver & """].TableA (a, b) Select 10, 10"

        db.Execute sql, dbFailOnError

    If this doesn't work, the final solution would be to use ADO to transfert your data but then, you'll have to have a loop and transfert each record individually.  You'll find numerous examples on the web on how to use ADO (caution: ADO, not ADO.NET) from Access VBA.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <programphases> wrote in message news:b9b5c436-80d1-40eb-b8ce-856a972a2317@communitybridge.codeplex.com...

    I am having a difficult time getting an Access table with 71 fields and 884046 records from an accdb database file imported into SQL Server 2008 R2. I have tried several different techniques for accomplishing this seemingly trivial task yet for some reason I have been unsuccessful. Here is what I have tried so far:



    1 - Export to Excel 97-2003 (xls) from Access 2010 and then Import into SQL Server using SQL Manager

    Result: This works without errors but only 65535 records get copied to the Excel file and then subsequently imported into the SQL table.



    2 - Use the SQL Upsizing Wizard in Access 2010, I selected Create New Database, created a dsn to SQL Server, did not select "only create table structure", did not select to create timestamp fields or any attributes, no application changes.

    Result: Error message "Table was skipped or export failed"



    3 - Create an external link to an existing SQL Server table so I can execute an insert command to copy the data from the Access table to the linked SQL Table.

    Result: Cannot open database 'tablename'. It may not be a database that your application recognizes or the file may be corrupt.

    Note I have tried this with several different tables from several different databases and get the same error. Strangely, this technique worked yesterday on this same server but will not work today.



    4 - Use SSMA 2008 for Access v4.0 to attempt to migrate the data from the accdb file to SQL Server.

    Result: Get the error 'There is nothing to process' when the 'Convert, Load, and Migrate' option is selected for the Access table.



    I don't care which of these four techniques I use for the migration, I just need at least one of them to work. Does anyone have any suggestions?

    Monday, July 26, 2010 12:10 AM
  • Thanks for the help...

     

    The problem was actually a corrupt Access database so this was an accurate error message:

    "Result: Cannot open database 'tablename'. It may not be a database that your application recognizes or the file may be corrupt."

    • Proposed as answer by Naomi NEditor Friday, August 13, 2010 8:16 PM
    Friday, August 13, 2010 7:58 PM
  • It's quite possible that the problem was indeed a corrupt Access database. However, this doesn't explain why you have been unsuccessful even after having tried this with several different tables from several different databases and get the same error.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Sunday, August 15, 2010 1:13 AM
  • Good point.  It is possible that I was inaccurate in my detailing of the evidence of the problem in 3.  I thought I had tested on other databases but I can't remember now.
    Thursday, August 19, 2010 3:43 PM
  • Alberto, this worked pretty well.  Thanx
    Friday, August 20, 2010 9:07 PM
  • Why so much difficulty to do a simple task?  In this day and age, with these products, we should be able to click a few boxes and import/export to any other MS product at will with no trouble.  Right now, in 2011, its become so convoluted that its not enjoyable to use these products.   Access 2010 should be able to talk to SQL 2008 R2 and vice-versa -- without any difficulties whatsoever.   But the reality is, its easier to train a horse than to use these products now.  I am so disgusted with this because I can not, for the life of me, get 31 Access 2010 tables into SQL Server 2008 R2.  I wish I could go back to SQL Server 2000 and Access 2003.  Things meshed so well and life was so much simpler.
    Tuesday, February 22, 2011 12:58 PM