[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Best method to import access data into SQL automatically

Posted on 2007-11-27
Medium Priority
Last Modified: 2010-04-21
I have a need to import/replace data automatically (hands off) several times a day into a live SQL server.

I have legacy data in both MS Access and Paradox DBF formats that has to be imported into SQL Server 3 times each day to keep the inventory "current". It needs to replace the data or at least update it where it is different.

I need it to be fast so it doesn't disrupt the websites that use the data.
 The way I am doing it right now is bound to give me trouble.

So far the best method I could come up with is a vbscript VBS file that deletes the current SQL Table data and then repopulates the same SQL Table from the msdb or dbf.  
I am fairly certain that is an inefficient way to go.

Ideally I need it to just update existing data and add new data, but I have no clue how to do that.
Scripting that in vbscript would probably take too many resources and cause many problems.

Any suggestions?

Question by:EGormly
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2

Expert Comment

ID: 20359326
I would create an 'import table'  loading your data from the two other databases, and then run update queries from sql.  I would even go so far as to write the SQL piece as a stored procedure, then write an application to run the stored procedure on a periodic basis.  In this manner the speed of updates will be preserved...

Author Comment

ID: 20360214
Do you mean:

SQL Server Tables:

Import Data from Access Database into TempDataMDB Table
Import Data from DBF Database into TempDataTableDBF
Use an Update querie to take data from the

and put into


Delete Data from Access Database into TempDataMDB Table
Delete Data from DBF Database into TempDataTableDBF

Thanks, but although that does preserve the speed, it does nothing about my need for a "How"
I am pretty much clueless on SQL and storing queries or the like.  
I am fairly decent at VBScript but where do I start?

I wouldn't know how to write an update query from one table to another
preserving all the intracasies of the data.  Should I still use VbScript to do the iomporting into the SQL temp tables?  I am still a bit lost on this.

Accepted Solution

the_b1ackfox earned 2000 total points
ID: 20363684
yes exactly...  as for the how, how much of the proposed solution do you already know how to do?  1) Import the data  2) Creat an update query 3) turn the query into a SP 4) clear the temp tables  5) write an app to run the SP

From your previous post, I think you are good on 1, 4 & 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 20365857
I have found the answers to most of my problem in the last few hours.
I learned how to create a stored procedure, learned how to move identical data from one table to another and learned how to execute it.

I only need to learn how to create a stored procedure that will import access data into an existing table.

So it's number 1 actually.

How to import Access data into an existing SQL Server Table

Expert Comment

ID: 20369499
Take a look at this to see if might be of any help.

Managing Data with Data Transformation Services

Expert Comment

ID: 20373666
Interesting...  So you're going to do exactly as I recommended but not give any pts.  To whom ever is moderating, I object.  It would be 1 thing if the solution was off base.  In this case the person asking the question has taken the advice.  I pity anybody whomever ends up with people like this as clients

Author Closing Comment

ID: 31411220
I didn't mean to annoy you with a request for cancellation.

I felt my question wasn't answered the way I wanted it to be.
perhaps that was my fault for not asking the correct question to begin with.

Before I  posted the question I was already using scripts to import the data (vbscript) into the live database.

You added that I should do it in a temp database.
While that was not a complete solution or a complete answer to my question, it should still qualify as points as it is a help.

I will re ask the remainder of my question in another post.

Expert Comment

ID: 20381186
I object as well for the same reasons that b1ackfox is stating. He responsed on more then one occasion to Egormly questions and guided him in the direction needed. Points should be rewarded to b1ackfox.

Author Comment

ID: 20381379
MeCanHelp .. way to go on commenting where not needed.
I awarded him the points before you posted that.. no need to add to a discussion once it is settled.

Author Comment

ID: 20381472
I have to clarify something...

I made a request to close this question in the lounge.
It was a mistake, I meant to ask to close 22985749.
This one needed to stay open so I could get the answer I needed.

When I saw the comment by the_b1ackfox about my decision to ask for closure I was annoyed at him without really realizing I asked to close the wrong question.

Compunding the problem.

I was a bit confused and then after re-reading the question made a snap comment about it not being complete. It wasn't complete but I should have instead just told him that I wasn't yet finished with the queston, I am sure the_b1ackfox would have answered the remainder of the question, but because of my successive errors... this is where we are.

the_b1ackfox: I apologize for the misunderstanding.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question