ODBC opportunity?

[rcollman]

Novice here. Yoda are you there?

I have to be doing something wrong. My Link VBA procdure took way less time than my Import procedure. I hate to do this but here is the nub of my import procedure.

DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=LittleRedCI;ServerName=10.10.10.100.1583;ServerDSN=LittleRedEI;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;;TABLE=workord", acTable, "WORKORD", "WORKORD1", False
DoCmd.RunSQL "delete * from workord ;", 0
DoCmd.RunSQL "insert into workord select * from workord1", 0
DoCmd.DeleteObject acTable, "workord1"


The above is really slow. Why is is
something like:
DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=LittleRedCI;ServerName=10.10.10.100.1583;ServerDSN=LittleRedEI;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;;TABLE=workord", acTable, "WORKORD", "WORKORD1", False

so much faster?

Enlightenment, please.

 

[Pat Hartman]

Linking simply creates a reference to data located in the ODBC data source. Importing actually transfers all of the table contents from one place to another. The difference is similar to sending someone a hyperlink to a web page rather than sending them the HTML contents of the page.

 

[rcollman]

Pat - 2nd thanks this evening.

Yep. I sort of knew that. Without taking out the stopwatch, both procedures seemed to take the same amount of time. Then it changed once I took out the stopwatch - by a factor of 10 or more!

Could be other gremlins at work like my network and power of all its components.

Linked files in their own database. Plug into those from another database and don't worry about relationships.

Thanks again. Chris