Access db to SQL

[DomTrix]

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

 

[Luvsql]

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.

 

[DomTrix]

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.

 

[wwwSQLOBSERVERcom]

I agree with Luvsql, DTS sounds like the right way to go but what do you mean by trigger? eg. only kick-off the ETL process when an Access db is loaded?

http://www.SQLobSERVER.com

the Microsoft BI/DW community

 

[DomTrix]

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

 

[wwwSQLOBSERVERcom]

Let me look into this one and get back to you. It should be quite possible with DTS.

Cheers

http://www.SQLobSERVER.com

the Microsoft BI/DW community

 

[RobertT687]

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.

 

[wwwSQLOBSERVERcom]

Sounds like plan. Good call Robert.

http://www.SQLobSERVER.com

the Microsoft BI/DW community

 

[DomTrix]

Great thankyou, a user would still need to open up access to do this though wouldn't they?

 

[RobertT687]

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.