By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,791 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,791 IT Pros & Developers. It's quick & easy.

Transfer records to a SQL SERVER 2005 db

P: 2
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.

Aug 26 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
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.

Aug 27 '08 #2

P: 2
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?
Aug 27 '08 #3

Expert 2.5K+
P: 3,072
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 )

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.

Aug 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.