Append Access Data to SQL Server Table

[Boyd]

Within Access 2003, I need to append data from a local Access table to
a linked SQL Server 2005 Express table. I have a VBA routine that
imports an Excel file into an Access table (about 5000 records), then
runs an append query to upload the data into my linked SQL table ...
the append is extremely slow!

My query is as follows:
DoCmd.RunSQL ("INSERT INTO [Linked SQL Table] SELECT * FROM [Access
Table]")

Because I'm using SQL 2005 Express, there is no DTS functionality to
import directly into SQL. Also, I thought I could somehow use a SQL
pass-through query from within Access, but don't believe I can
reference an Access table in the QueryDef sql property.

Does anyone have an idea how to expedite the processing of my append
query? Any ideas are greatly appreciated.

Thanks,
Boyd

 

[a a r o n . k e m p f]

move to Access Data Projects.
and just use Docmd.TransferSpreadsheet.

ADP allows you to use the native MS Access commands in order to import
stuff _DIRECTLY_ into SQL Server.

Thanks & Good Luck-

-Aaron

 

[Boyd]

Thanks Aaron. I thought about using ADP's, but probably don't have
enough time to implement before the deadline I've been given to
complete the app. If I do go the ADP route, why wouldn't I run into
the same slowness issues as I am with linked SQL tables? The data
still needs to travel across the network to the SQL Server, correct?

Boyd

 

[a a r o n . k e m p f]

no dude.

Slowness is just a symptom of using an obsolete database.
The kids around here-- blame it on the network.

Any database that doesn't work well over a network-- just plain
'shouldn't be used'.

Importing a 5000 row spreadsheet into SQL Server- using ADP- it would
take less than a second I'm sure.

-Aaron

 

[a a r o n . k e m p f]

Linked tables are inherently slow.

There is nothing you can do with a linked table that isn't slow.

-Aaron

 

[Boyd]

I appreciate the feedback, and will look into ADP's.
Thanks Aaron.

 

[Tony Toews [MVP]]

Thanks Aaron. I thought about using ADP's, but probably don't have
enough time to implement before the deadline I've been given to
complete the app.

Be advised that Microsoft did not do any enhancements to ADPs in
A2007.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Within Access 2003, I need to append data from a local Access table to
a linked SQL Server 2005 Express table. I have a VBA routine that
imports an Excel file into an Access table (about 5000 records), then
runs an append query to upload the data into my linked SQL table ...
the append is extremely slow!

My query is as follows:
DoCmd.RunSQL ("INSERT INTO [Linked SQL Table] SELECT * FROM [Access
Table]")

Currentdb.execute "SQL string", dbfailonerror may be faster and will
give you error messages which docmd.runsql will not.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

Tony;

where do you get off with this mis-information?
ADP _HAS_ had plenty of improvements all along.

MDB didn't have a single enhancement for 3 versions-- 2002, 2002,
2003.

So what exactly are you trying to say, Tony??

-Aaron

 

[Tony Toews [MVP]]

Tony;

where do you get off with this mis-information?
ADP _HAS_ had plenty of improvements all along.

MDB didn't have a single enhancement for 3 versions-- 2002, 2002,
2003.

So what exactly are you trying to say, Tony??

You are totally wrong.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

You are totally wrong.

Aaron

You are totally wrong.

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

Tony;

where do you get off with this mis-information?
ADP _HAS_ had plenty of improvements all along.


MDB didn't have a single enhancement for 3 versions-- 2002, 2002,
2003.


So what exactly are you trying to say, Tony??


-Aaron

 

[Tony Toews [MVP]]

where do you get off with this mis-information?
ADP _HAS_ had plenty of improvements all along.

Post URLs

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/