ADO Batch Update

[Tallboy]

Hi Guys,

Does anyone know where I can get the ADO code to upload records from local tabel in MS Access to a tabel on my SQL Server, there will only be about 20 rows at a time.

I was thinking of creating a recordset RS from the Sql Table.

Then use sql statement like

INSERT(..Field Names..) INTO (SELECT * FROM LocalTable) RS

Then RS.UPdate

Anyway advice appreciated.

Regards

 

[mdlueck]

I would suggest if possible you use a Stored Procedure (SP) to INSERT the new records into SQL Server. I use ADO code to drive the SP from Access.

Reasons I like SP's:

1) All date is encapsulated in Parameters objects so the data does not need to be bare on the line of SQL being executed. Safer to guard against SQL injection attacks, etc... Mismatched quotes do not crash your app as well.

2) Faster! SP's are prepared by the server so get to skip the prepare step that dynamic SQL passes through.

I posted here some code to drive an INSERT SP with:
http://www.access-programmers.co.uk/forums/showthread.php?t=218229

I think my code in the example is fine, and perhaps records on the AS/400 were changing and causes the errors. I ran the process a few hours ago and this time it completed perfectly. (shrug)

 

[Galaxiom]

I would suggest if possible you use a Stored Procedure (SP) to INSERT the new records into SQL Server. I use ADO code to drive the SP from Access.

How is SQL Server Stored Procedure going to know about the data in the table in Access?

 

[Galaxiom]

I was thinking of creating a recordset RS from the Sql Table.

Then use sql statement like
INSERT(..Field Names..) INTO (SELECT * FROM LocalTable) RS

An SQL command cannot be run against a recordset.

The easiest way is to link to the SQL table via ODBC (External Data -ODBC) then run an ordinary Access Insert query from the Access table.

Another way is to create a recordset on the Access table and loop through it, inserting the records into the server table using an ADO Command. A variant of this is to open a recordset on the server table and AddNew records to it in the loop.

 

[bparkinson]

All these ideas will work, and a parameterized stored procedure is the best way, I think. Better than that, however, is to pull the data out of the Access DB using either a Data Transformation Services (DTS) or a SQL Server Integration Services (SSIS) package, depending on your version of SQL Server. Both services know how to open an Access DB, retrieve data, and put it wherever you want in SQL Server. Big advantages of either DTS or SSIS is that once your package is created, it can be scheduled to run when it needs to, it's execution is logged, SQL Server can email you if it fails, and so on.

 

[Galaxiom]

Big advantages of either DTS or SSIS is that once your package is created, it can be scheduled to run when it needs to, it's execution is logged, SQL Server can email you if it fails, and so on.

This sounds interesting. I am currently planning to migrate a huge Access backend database to SQL Server. It includes a daily operation to extract data from a text file and load it into the database. Being able to schedule that inside the server would be great.

It seems SSIS would be the way to do it. The learning curve looks significant but definitely worth the effort. Thanks for the tip.

I have downloaded and started reading Karthikeyan Andbarasan's free ebook. Any other resources you could recommend?

The impression I have so far is that SSIS supercedes DTS.

 

[mdlueck]

How is SQL Server Stored Procedure going to know about the data in the table in Access?

VBA code will read from the Access table and call the Stored Procedure. This is replacement for doing a SQL INSERT to SQL Server via an ADO object... run the SP instead.

 

[bparkinson]

This sounds interesting. I am currently planning to migrate a huge Access backend database to SQL Server. It includes a daily operation to extract data from a text file and load it into the database. Being able to schedule that inside the server would be great.

It seems SSIS would be the way to do it. The learning curve looks significant but definitely worth the effort. Thanks for the tip.

I have downloaded and started reading Karthikeyan Andbarasan's free ebook. Any other resources you could recommend?

The impression I have so far is that SSIS supercedes DTS.

Galaxion:

I've used SSIS on one huge project, and the learning curve is steep. You build SSIS packages in Visual Studio, and it's such a rich environment that it's daunting at first. It was designed for huge IT shops where there are dedicated SSIS developers.

SSIS imports text files brilliantly, but you need to roll your own file handler. It can be in SSIS, or a SP, or wherever you like. It's easy though.

SSIS replaces DTS. DTS was great and easy to use, but it's not the enterprise tool that SSIS is.

I didn't buy a book, just referred to the SSIS chapter in the comprehensive SQL Server 2010 book I was using. It was enough to get me productive.

To really take advantage of SSIS, you'll need SQL Server Standard Edition so you can schedule your daily job(s).

I cannot remember the book I had. It was big enough to stun an ox as they usually are. Travel to a bookstore and buy one with the biggest and most confounding chapter on SSIS, that's what I did.

Feel free to contact me. I'd be happy to answer your questions if I can as you get ramped up, night save you a little time. Good luck with your migration.

Bob

 

[Galaxiom]

VBA code will read from the Access table and call the Stored Procedure. This is replacement for doing a SQL INSERT to SQL Server via an ADO object... run the SP instead.

But the stored procedure would not have the ability to read the Access database would it? (Aside from using SSIS as Bob indicated).

VBA would have to pass parameters and run the stored procedure repeatedly while looping through a recordset based on the Access table to get the parameters.

Is there any advantage to using the SP over an ADO command in this situation?

 

[Galaxiom]

Feel free to contact me. I'd be happy to answer your questions if I can as you get ramped up, night save you a little time. Good luck with your migration.

Thanks Bob. My strategy is to arm myself with as much knowledge as I can before I start on it.

I have done a little with SQL Server but nothing ambitious or beyond the capabilities of Express. We recently moved up to SQL Server Standard for a third party application so I thought it was time to get a bit more serious about it.

 

[bparkinson]

Thanks Bob. My strategy is to arm myself with as much knowledge as I can before I start on it.

I have done a little with SQL Server but nothing ambitious or beyond the capabilities of Express. We recently moved up to SQL Server Standard for a third party application so I thought it was time to get a bit more serious about it.

That's how I learned it, from a commercial app we bought. You are perfectly positioned to become a SQL Server DBA. It's a good thing to be.

There's probably a SQL Server users group if you are in a city. Having colleagues to bounce things off is a big advantage, especially in the beginning. Again, ask me anything you want. I'n happy to help.

 

[Tallboy]

OK Guys,

Here is it the final solution to transfering data from an ms access table to a SQL server table --- 6 simple lines-- it was easy in the end, but took me 3 hours of trial and error!
I am using windows Authntication you may need to put in usernm an password so I have a commented alternative with it !

Just Chane the SQL to suit you applciation enjoy its truly amazing to see it operatei I only have alittle data but woud love to hear how it handles lots of data !

Sub TransferData()
  'Here it is short and sweet
  'How to Tranfer buuld data from an MS Access Table to a SQL Server table
  
  Dim db As Database
  Dim strSQL As String
  Set db = CurrentDb
  
' TEMPLATE INSERT  INTO <Put ISAM Name Here> FROM <normal query syntax to connected DB and table>
' SQL Server [ODBC;Driver={SQL Server};SERVER=<Server Name>;DATABASE=<DatabaseName>;UID=<UserName>;PWD=<password>;].[Table]

strSQL = "INSERT  INTO [ODBC;DRIVER={SQL Server Native Client 10.0};SERVER=LYPC-115536\HCSSQLDB1;DATABASE=HCSDB1;Trusted_Connection=Yes;].[tblClients] SELECT * FROM tblClients"
  
Debug.Print strSQL

db.Execute strSQL, dbFailOnError
 
 Debug.Print "Transfer Completed !"
  
 ' Dont forget to delete data from ms access tabel if rquired
 End Sub

 

[Galaxiom]

Ah yes. A DSNless ODBC connection. Well done. Good choice. That is another way we didn't think about and went off the track discussing the benefits of Stored procedures.

Essentially it the equivalent to a temporarily linked table using ODBC. Performace should be good.