Answered by:

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?

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:

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:

(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.)

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.

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:

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.

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:

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:

(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.)