ADO Connection to SQL Server - Upload Data 1

[ideafixer]

I have a table in Access and a table in SQL/Server. I have set up an ADO connection to SQL server to populate my SQL Server table. I have set up a DAO connection to my Access table. I am accomplishing the upload by setting the value of each field in the SQL/Server record set to the value of my Access record set then using the .Addnew method on the SQL/Server record set.

This seems very slow. Is there a way to insert all of the records from my Access Table into the SQL/Server table through an insert statement? To accomplish this do I need to define my Access table as an ADO recordset? Looping through all of the records seems very inefficient.

 

[cmmrfrds]

There are lots of options in addition to your option.
(1) OpenRowSet
(2) Linked server where the access table is linked on sql server
(3) Link the sql server table in access
(4) Batch insert
These are 4 options off the top of my head there are probably others.

The 1st 2 will be the fastest since most of the processing is done on SQL Server.
The 3rd is a common practice.
If the recordset is not extremely large you could try a batch update since it would require very little change to your code.
rs.LockType = adLockBatchOptimistic
rs.CursorLocation = asUserClient
After opening the sql server connection basically what you need to do is:

rs.ActiveConnection = Nothing
'- enter your update loop and do all the updating
'- leave the .update at the end of the loop
'- after update loop
rs.ActiveConnection = your sql server connection
rs.UpdateBatch
rs.close
set rs = Nothing

If you want to try 1 of the first 2 options let me know and I can give you an example.

 

[ideafixer]

I want to avoid a link since the location of my data source can change. That is why I am using the ADO connection. If that was not the case, I would set a link up. Options 1 or 4 look as if they can go through the ADO connection. The volume of records updated to the table can range from 100 - 25,000 and even greater (in rare circumstances)

Could you please emphasize on example 1 or 4.Which ever one is more efficient accessing the data through ADO. I am assuming option 1.

Thanks!

 

[cmmrfrds]

Options 1 & 2 are comparable since in both cases SQL Server establishs an OLE-DB connection to Access. Option 2 is the easiest to work with from the application since the Access table is linked on the sql server side it looks to your access program just like an ADO connection to another sql server table. The downside is that the sql server dba needs to set up the access table as a linked server and the dba might not want to do it. This is one more thing for them to consider in the sql server maintenance.

The OpenRowSet is still done on the sql server side but asks sql server to make the connection at runtime. This is probably more dynamic and flexible for your needs. I will paste in an example using the Northwind database which you should have on your system. First on option 4, it is still all done on the client side but the update to sql server is done in 1 batch. For 20K rows, I would go with options 1 or 2. In the example the access mdb is on the sql server pc.

Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[cmmrfrds]

I forgot to mention I am using sql server 2000. I am not sure if the older versions support OpenRowSet.

 

[cmmrfrds]

Sorry about all the posts but I just thought of another option that may be relatively fast and should work on sql server 7 as well as sql server 2000. That is to use a temporary table. It will be a little slower than options 1 & 2, but probably not much. I am confident it will be faster than your current setup.

All you need to do is keep the connection open between 2 processes. One build the temporary table and then use the same connection to run the update stored procedure that joins the temporary table to the sql server table.
(1) Establish ADO connection to sql server
(2) Build the temporary table in sql server
(3) execute the stored procedure to update the table
(4) drop the temporary table

I have done this but don't know if I have a ready example available. Let me know and I will look around.

 

[ideafixer]

I am using SQL version 7. In reference to your row set solution, is that looping through the entire record set to insert the values from my Access table into the SQL table? I am essentially doing that by looping through the Access record set then using adnew to update the SQL record set. Or is the methode you explained updating a batch of records to the SQL table?

 

[cmmrfrds]

The openrowset allows you to handle the access table as one of the tables in an append type query from sql server.

Basic idea illustrated without the openrowset.

Insert into servertable (x, y, z)
Select x, y, z from accesstable

Add the openrowset to this type of query as per the prior example and all the processing is done on the sql server pc. It is not at all like the addnew. It accomplishes the same results but more efficiently.

 

[ideafixer]

I used the UpdateBatch method. Seems to be working pretty well. However I am interested in your suggestion for executing a stored procedure in SQL/Server. Once my data is uploaded, I downloaded from the database to perform a compare.

To accomplish this, I open two tables in SQL/Server through ADO. Then I loop through the record set searching for differences in specific fields. I have written an SQL statement that returns the same results. Unfortunately when I attempt to open my query as a record set, I get a time out error.

I can write the query on query analyzer. It takes a few minutes to run, but does give me the correct result set. I was thinking about executing this query on the server then making a table. This table would end up as my record set for download into access.

I would have to pass parameters to my query. I am assuming the query would be a stored procedure. Any suggestions?

 

[cmmrfrds]

If the stored procedure returns a resultset then that results can be treated like any other ADO recordset.

Set rst = cn.Execute "mp_storedprocedure"

One thing to be aware of on a sproc from sql server is that additional information can be sent back to the application. If you "set nocount on" in the sproc this gets rid of that info and the confusion it could cause.

The ADO connection has a default timeout of 30 seconds. The ADO command, typically used with sproc's, has a default of 15 seconds.

cn.CommandTimeout = 300 '- this is 5 minutes
cmd.CommandTimeout = 360 '- this is 6 minutes
cn.CommandTimeout = 0 '- this is no time out