Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access db to SQL

Status
Not open for further replies.

DomTrix

Programmer
Dec 28, 2004
94
GB
We have setup an online shop for a client and are using a SQL server database for all their stock, etc. They receive a monthly stock update from there main supplier which is stored in an access db on their pc's at their bricks and mortar shop.

I am looking to the most efficient way of updating the SQL database (on a shared hosting server) from the Access db on the clients PC.

At present, the client uploads the access mdb file to a location on the server which we have set up as a DSN and I run some Coldfusion code to extract the data, query it and then update the SQL database. This worked on our dev server but is timing out on the shared server (process takes around 1-2 hours).

I think this is probably a most ineficcient way to do things, can anyone point me to a better method?

Thanks in advance

DT
 
You could setup a DTS package that will query the access database, then update the sql tables. This package can be setup on a scheduled basis and the incoming data can be manipulated with code to correspond with the sql tables etc.
 
Would it be possible to trigger a DTS package? There is no definate schedule for it and there may be several updates in a month. There will be no technical person involved, the entire process must be initiated and controlled by the non techie client.
 
Yes kinda, the client should initiate the process. I.e. it is not viable to have it on a scheduled time, it must be 'triggered' by the client's action.

Thanks,

DT
 
This may help you get started.
What I have done in situations like this is to create a Job that runs the DTS package.
Then from Access, create an ADO connection (cnn) to the msdb database and execute the job like this: cnn.Execute ("EXEC sp_start_job @job_name= 'YourJobNameHere'")
There are probably many other ways, but I like this approach because it creates a job history.

 
Great thankyou, a user would still need to open up access to do this though wouldn't they?
 
Not necessarily. The same effect can be achieved with an OSQL script that executes the same sp_start_job procedure. Again, there are probably many ways to do this that don't require Access.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top