DTS Import from Access

Question

DTS Import from Access

EamonSQL

SSCrazy

Points: 2307
More actions
November 13, 2006 at 3:16 am

#67955

Hi,
I'm writing a DTS package that gets called from a button in an Access app. The DTS package first imports some tables from Access before continuing.
The problem here is that the Access db is open and I'm getting an error to that effect. Is there an ODBC setting somewhere I can set or an alternative that I can use to allow the import to progress.
Any help welcome.
Eamon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

the sqlist SSCrazy Points: 2398 More actions · Answer 1

"Imports some tables from Access"; from what Access, the same db? To the same db? What is your source and what is your destination?

There are lots of examples of vb code that executes a dts package. You can use one in your button click event in Access. You just have to do a search on google or more precisely on http://www.sqldts.com.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Bill Scott SSCrazy Points: 2338 More actions · Answer 2

You may have to create a seperate access database, populate the tables, then close that database and call the DTS package. DTS doesn't like to work with files that are open.

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

Hi Guys,

Here the MS Access VBA code I used:

'-------------------------------------------------------------------

' Access VBA code for MyButton

Dim oPkg, oStep, sMessage, bStatus

Const DTSSQLStgFlag_Default = 0

Const DTSStepExecResult_Failure = 1

Set oPkg = CreateObject("DTS.Package")

oPkg.LoadFromSQLServer \\Myserver_name\MyInstance, "MyUsername", "MyPwd", 0, , , , "DTS Package name"

oPkg.Execute

Set oPkg = Nothing

---------------------------------------------------------------------

The DTS package does some processes that includes importing data from the Access db where the button lives.

Hope this helps,

Eamon

John McC Hall of Fame Points: 3410 More actions · Answer 4

As Bill said DTS does not like open files so another option rather than a separate Acces database would be a VB front end.

I have an Access front end whose tables are linked to text files and are updated by DTS. If the database is open, package fails. So there is a .bat to kill Access prior to the update.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

EamonSQL SSCrazy Points: 2307 More actions · Answer 5

Yes, I had the same problem and have a copy of the files I use in Access as linked tables. before the call to DTS I move the data I require to the linked tables so as to avoid locking and when the DTS packages completes then I simply update the resulting data back to the affected original tables.

Yes, I like the .bat approach to kill Access but the app needs to remain open in order to succeed.

' hope this helps.

Eamon