none
Converting Access Data to SQL Server

    Question

  • Using Access 2010 Access Data projects tried to import several tables from an mdb file to sql server express 2005. Some tables transferred but the largest table did not. Just hangs up. The table was created but no data transferred. Then tried to write vba code to connect to the mdb file and received errors could not find data source. Is it possible to create a connection to an mdb file from Access Data project? 
    Tuesday, June 21, 2011 1:15 PM

Answers

  • Not necessary, if you're using certain types of key (or if there are certain types of foreign key available on that table). For example, if you have an AutoNumber field, your batch numbers could simply be:

    key_id_pk \ 65536
    


    where "\" (backslash) is the integer division operator. There are various ways you can check that all your data have been transferred correctly, after doing the transfer operation. To check each record has been transferred, for example, you could include this expression in an aggregate query such as:

    SELECT Count(key_id_pk), key_id_pk \ 65536 AS batch_number FROM table_name GROUP BY (key_id_pk \ 65536) ORDER BY (key_id_pk \ 65536);
    


    If your key_id_pk field is appropriately indexed, I think this query should not be too expensive (correct me if I'm wrong in theory—I've tried such things in practice and they worked okay. I have a test dataset with 2^30 records, and used similar techniques for this—the transfer went fine.)

    One might compare the results of this query between Access and SQL Server. (Please note that the integer division operator might be different in SQL Server from what it is in Access/ VBA.)

    For most databases, the best way to insert such batches into SQL is going to be an SQL statement like this:

    INSERT INTO dbo_tablename (...) (SELECT ... FROM tablename WHERE key_id_pk>=batch_min_id AND key_id_pk<batch_max_id);
    


    (Naturally, if you're doing this in VBA, you'll construct the SQL query as a string before executing it.)

    I've used 65536 here as the batch size, but for most data sets, a larger number, somewhere in the range from 2^20 to about 2^24 might be most appropriate. Anywhere above 2^24 and SQL Server queries start taking a disproportionately longer time to transfer the records (I presume, because of issues with hard drive space and virtual memory used to prepare for the SQL transaction, on my 4GB machine.)


    Matthew Slyman M.A. (Camb.)
    • Proposed as answer by Bruce Song Monday, June 27, 2011 11:45 AM
    • Marked as answer by Bruce Song Friday, July 08, 2011 11:32 AM
    Tuesday, June 21, 2011 6:56 PM

All replies

  • That's very complicated. Much easier to use "SQL Server Migration Assistant for Access" from microsoft.com

     


    -Tom. Microsoft Access MVP
    Tuesday, June 21, 2011 1:52 PM
  • The underlying problem might be the amount of data. SQLServer may not be hanging, but just taking some time to process it all.

    1) Do a copy and past of the table in Access where you copy the structure only (NO DATA) naming it something like [originalFileName_1]

    2) Add one row of data

    3) Try upsizing again, the table should be created. Its been awhile since I've actually upsized a db, but I believe that you can select which tables to upsize. Obviously, don't select the table with the data. Once the upsize is complete, change the file name to the correct name.

    4) Using the 'Import Data' task to pull over the actual data.

     


    David H
    Tuesday, June 21, 2011 1:57 PM
  • If it is a not frequent task and you just want to import some data once, it's easier to use built-in SQL Server Management Studio Import Wizard. I think David gave the easiest and the fastest way. I did my migration so:

    1. With built-in MS Access Upsizing Wizard I transfered a structure only (w/o data).

    2. With built-in SSMS Import Wizard I transfered all the data to prepared empty tables.

    One issue I faced that time, if a table has AutoNumber field, SSMS don't want to trasfer the data. So, while setting up the wizard, I change the SQL statement from SELECT * FROM MyTable -> SELECT Field1, Field2, ... , FieldN FROM MyTable (all the field except this AutoNumber) and all is ok.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, June 21, 2011 2:07 PM
  • Sometimes, if I'm transferring massive amounts of data; I'll do this:

    1. Create appropriate indexes on the tables (if they don't exist already),
    2. Write a VBA subroutine to transfer the data a little at a time (with DoEvents statements between each batch, and with a simple progress indicator implemented for example with Debug.Print statements).

    Matthew Slyman M.A. (Camb.)
    Tuesday, June 21, 2011 4:53 PM
  • Sometimes, if I'm transferring massive amounts of data; I'll do this:

    1. Create appropriate indexes on the tables (if they don't exist already),
    2. Write a VBA subroutine to transfer the data a little at a time (with DoEvents statements between each batch, and with a simple progress indicator implemented for example with Debug.Print statements).

    Matthew Slyman M.A. (Camb.)
    When you do that, do you add a column in which you tag which batch the record was in to help if there's a problem with that specific batch?

    David H
    Tuesday, June 21, 2011 4:56 PM
  • Thank you for the response. Is Migration Assistant a product I can download. I did not see this as an option in SQL Server Express 2005?
    Tuesday, June 21, 2011 5:42 PM
  • Thank you for responding.

    The table does have an autonumber field and I need to maintain the numbers. Should I change the field to a number field before uploading?

    I am trying to convert to SQL Server 2005 Express Edition. Is the SSMS built in for this product? I could not find it?

     

    Thanks

     

    John

     

     

    Tuesday, June 21, 2011 5:46 PM
  • John, as far as I remember, SSMS is included into Express Edition since 2008 version. You can try to install the latest 2008 R2, as always for free. 

    http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

    Here is a page about migration http://www.microsoft.com/sqlserver/en/us/product-info/migration.aspx

    And here is the latest 5.0 version of SSMA (also free) http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4b37df74-7522-41cf-9c1a-01b6415d9608


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, June 21, 2011 5:56 PM
  • Thank you Andrey

    Much appreciated.

     

    John

     

    Tuesday, June 21, 2011 6:21 PM
  • Not necessary, if you're using certain types of key (or if there are certain types of foreign key available on that table). For example, if you have an AutoNumber field, your batch numbers could simply be:

    key_id_pk \ 65536
    


    where "\" (backslash) is the integer division operator. There are various ways you can check that all your data have been transferred correctly, after doing the transfer operation. To check each record has been transferred, for example, you could include this expression in an aggregate query such as:

    SELECT Count(key_id_pk), key_id_pk \ 65536 AS batch_number FROM table_name GROUP BY (key_id_pk \ 65536) ORDER BY (key_id_pk \ 65536);
    


    If your key_id_pk field is appropriately indexed, I think this query should not be too expensive (correct me if I'm wrong in theory—I've tried such things in practice and they worked okay. I have a test dataset with 2^30 records, and used similar techniques for this—the transfer went fine.)

    One might compare the results of this query between Access and SQL Server. (Please note that the integer division operator might be different in SQL Server from what it is in Access/ VBA.)

    For most databases, the best way to insert such batches into SQL is going to be an SQL statement like this:

    INSERT INTO dbo_tablename (...) (SELECT ... FROM tablename WHERE key_id_pk>=batch_min_id AND key_id_pk<batch_max_id);
    


    (Naturally, if you're doing this in VBA, you'll construct the SQL query as a string before executing it.)

    I've used 65536 here as the batch size, but for most data sets, a larger number, somewhere in the range from 2^20 to about 2^24 might be most appropriate. Anywhere above 2^24 and SQL Server queries start taking a disproportionately longer time to transfer the records (I presume, because of issues with hard drive space and virtual memory used to prepare for the SQL transaction, on my 4GB machine.)


    Matthew Slyman M.A. (Camb.)
    • Proposed as answer by Bruce Song Monday, June 27, 2011 11:45 AM
    • Marked as answer by Bruce Song Friday, July 08, 2011 11:32 AM
    Tuesday, June 21, 2011 6:56 PM
  • Finally upgraded to SQL Server 2008 Express. Conversion worked nicely except for some date fields. Will look into this further.

    Thanks.

    John

    Thursday, June 23, 2011 9:40 AM
  • Yes. Search for it at search.microsoft.com.
    -Tom. Microsoft Access MVP
    Friday, June 24, 2011 4:19 AM