0

I need to create a facility to enable users to repeatedly transfer the contents of tables in an .mdb to identically structured tables in SQL Server (emphasis on 'user' and 'repeatedly').

  • I wish to avoid the 'built-in' management tools of SQL Server
  • My preference is for using SQL Server express
  • I don't want to re-create the SQL database or table structures each time

Is my only option a loop through the rows of the .mdb table, while INSERTing its contents line by line?

If the answer is yes, what limit can I expect to the number of rapid INSERTS?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
srm2710
  • 43
  • 3

1 Answers1

1

You don't need to insert the rows one by one.

Link the SQL Server tables into Access with something like

DoCmd.TransferDatabase _
    TransferType:=acLink, _
    DatabaseType:="ODBC", _
    DatabaseName:="your ODBC connect string", _
    ObjectType:=acTable, _
    Source:="dbo.Table1", _
    Destination:="dbo_Table1", _
    StructureOnly:=False, _
    StoreLogin:=True

and then insert all rows with one command from mdb to SQL Server:

INSERT INTO dbo_Table1 SELECT * FROM Table1

If you have IDENTITY columns, things get more complicated.
Find some hints here: Pass-through queries and linked table queries using different connections

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Sorry, I forgot to specify that in mdbs themselves are generated by software. I have no control over placing code etc in the mdbs. – srm2710 Sep 21 '16 at 13:59
  • @srm2710: If you want to automate this process, you will have to run code *somewhere*. E.g. have a "TransferControl" mdb with code, where you link the tables from each data mdb plus the SQL tables. – Andre Sep 21 '16 at 14:07