Feedback req: Considering non Pass-Through query to bulk populate SQL Server tables

[mdlueck]

Greetings,

I am hesitantly considering use of a non-Pass-Through query to bulk populate SQL Server tables. All other areas of BE DB SQL access are via Stored Procedures (SP's).

To publish records from Access to SQL Server, presently I fire an INSERT SP for each record to be populated to SQL Server.

If I were to change the design, I could use a Linked Table to SQL Server, then perform a SELECT / INSERT query.

1) Will the SELECT / INSERT query make a significant performance improvement?

2) Data in the production ERP system which is the source - downloaded into Access via nested DAO.QueryDef objects - does not have uniqueness constraints upon it. I have in my present INSERT SP's logic to check before INSERT, if present then do nothing, else perform the INSERT. I perform an ORDER BY in my Access query to insure that the proper row gets placed first, and subsequent rows are safe to skip.

With the Access tables having key violation data in it, and SQL Server having the keys, is there anyway to have Access ignore key violation errors, and yet successfully report other types of errors? These queries will be run in DAO.QueryDef objects.

3) Finally, I have not used Linked Table objects / non Pass-Through queries ANYWHERE in this application. Any sort of things I should be aware of that would steer me towards sticking with my present design which is "slow and steady which wins the race"?

Thank you in advance.

 

[CJ_London]

Will the SELECT / INSERT query make a significant performance improvement?

It may well depend on your data but I have some routines output to .csv file, so use a while not.eof loop to insert individual records and another which works using linked tables - the latter is faster.

I'm just investigating using a DAO recordset with the connection string built into the query and an ADODB connection as alternatives to having a linked table but too soon to report whether this is better (my objective is to lose the linked tables)

Which version of Access are you using? my understanding is that the JET links to SQL server can be slow

 

[mdlueck]

I'm just investigating using a DAO recordset with the connection string built into the query and an ADODB connection as alternatives to having a linked table but too soon to report whether this is better (my objective is to lose the linked tables)

Say, that is right... I could develop the query in an adoCMD object since both tables would be considered local to the Access FE DB.

How would you execute a query to directly transfer between Access --> SQL without using linked tables? If not linked tables, then in my mind one is left with a loop around many calls to INSERT, which is what I have presently.

Which version of Access are you using? my understanding is that the JET links to SQL server can be slow

Windows XP and Access 2007 on this particular machine.

 

[CJ_London]

How would you execute a query to directly transfer between Access --> SQL

I would look at the dao method which would be something like:

INSERT INTO (myConnectionString).mySQLTable (fld1, fld2, fld3)
SELECT myfld1, myfld2, myfld3
FROM myAccessTable

However I have not tested this as yet so may not work. Also, a quick look around seems to suggest that DAO would be slower since it is controled client side rather than server side.

Not sure what your connection string would look like but something like

Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

 

[mdlueck]

I would look at the dao method which would be something like:

INSERT INTO (myConnectionString).mySQLTable (fld1, fld2, fld3)
SELECT myfld1, myfld2, myfld3
FROM myAccessTable

Intriguing technique. I had never seen the "(myConnectionString).mySQLTable" type syntax before. So by using that, you think it is possible to avoid Linked Tables altogether?

And I have successfully working DAO connection strings as I use a nested DAO.QueryDef technique to download records quickly into the Access DB. So I will simply cook up a like connection string to connect to the SQL Server target DB.

Also, a quick look around seems to suggest that DAO would be slower since it is controlled client side rather than server side.

Of course client-side. Anything to do with an Access table must be client side.

 

[CJ_London]

As said - not tested!

Found this link which may help

http://www.pcreview.co.uk/forums/connection-string-sql-server-vba-t3895235.html

 

[mdlueck]

Intriguing technique. I had never seen the "(myConnectionString).mySQLTable" type syntax before. So by using that, you think it is possible to avoid Linked Tables altogether?

Access does not seem to be appreciating such syntax as you suggested.

I have not found mention of such syntax being supported. Could you please provide some references?

 

[SQL_Hell]

Isn't the above MYSQL syntax rather than SQL Server?

What is the backend for the ERP system.

If it was me doing this I would ignore MS access all together for this import, and use the SQL server import / export wizard to create an SSIS package that imports data from ERP system straight into SQL server. That would be quicker than INSERT INTO SELECT and obviously a lot quicker than doing 1 by 1.

 

[mdlueck]

Isn't the above MYSQL syntax rather than SQL Server?

It might indeed be. I had never seen such syntax.

What is the backend for the ERP system.

BE for my ERP / BI system is SQL Server 2008. The production ERP runs on an iSeries, thus DB2/400.

I am only allowed ODBC connections to both servers. The servers themselves are IT controlled.

I have no access to any SS*S services to use with my ERP / BI application.

My contract is suppose to wrap up tomorrow, so it shall remain publishing to SQL Server via INSERT SP's.

 

[CJ_London]

I have not found mention of such syntax being supported. Could you please provide some references?

Sorry - the web seems quiet on the subject and as stated before, not tested - but this SQL works to insert data from a table in the current db into another password protected Access DB - my thinking was the bit in square brackets could be replaced with the equivalent connection string to SQL server

INSERT INTO [C:\Test Databases\TData.accdb;PWD=mypassword].tblTest ( ID, TName, Field1 ) 
SELECT ID, SName, Field1
FROM myTable

 

[mdlueck]

INSERT INTO [C:\Test Databases\TData.accdb;PWD=mypassword].tblTest ( ID, TName, Field1 ) 
SELECT ID, SName, Field1
FROM myTable

Oh, that type of syntax is where your thoughts were going. Thank you for explaining.

 

[CJ_London]

Oh, that type of syntax is where your thoughts were going

Sorry to disappoint

 

[mdlueck]

Sorry to disappoint

Quite the contrary... clarative, not disappointing in the least.