Transfer records to a SQL SERVER 2005 db

[Bill Eddings]

I am new to the site so pardon me if I do things wrong until I learn. Though this question is not specifically to ACCESS it is very near to it so I am posting it hoping I get a reply.

What I want and need to do is update a SQL SERVER 2005 db from an EXCEL 2003 file using VBA. Since I am new to SQL then I am struggling with how to do this. I am well versed in VBA. The db has already been setup and each time run the code runs I will only be sendng new records. So, if anyone has some sample code that let me send about 10 records at a time to a SQL SERVER 200 db from an EXCEL file then please share. This code will run from within the EXCEL file that has the 10 records. This process will occur many times during the day.

If this post needs to go to a better forum or location then please tell me what that is and I will post there and accept my apologies for cluttering this site.

Thanks in advance from a new user.

Bill

[nico5038]

Why use VBA ?
The general approach is to define a DTS (Data transformation service) in MS SQL and use that for the transformation.
When it's a once off job, just right-click the table and use the import feature.
When you need it on a certain frequency you can schedule a job for doing this from MS SQL.

Nic;o)

[Bill Eddings]

Because what I am doing is written in VBA and is controlled by VBA. Many users are using my pogram at one time and each user's program will send a batch of records to the db periodically during the day. The db in SQL SERVER is just and only the warehouse for the data. And since I know very little about what SQL can do for me if only SQL were used then I am pursuing the route I have taken. Make sense?

[nico5038]

As always, there are many roads that lead to a solution here.
One is to use linked tables to both the Excel dataset and the target table in MS SQL, another to start an Access data project (check http://msdn.microsoft.com/library/de...essProject.asp )

As you're obviously in the VBA "department" I guess the linked table approach is best to handle. All needed is to create a mechanism to link the correct excel file and to execute the query to append the excel data to the MS SQL table.

A simple linking mechanism is to use a fixed excel workbook name and have the user file overwritten each time. Problem with this is you'll need to make sure the same data isn't added twice. This can be "solved" by adding / checking a date or runnumber in the userfile.

Nic;o)