Dynamically Connect/Disconnect to Access Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Dynamically Connect/Disconnect to Access Database

Author

Topic

JuniorProgrammer
Starting Member

10 Posts

Posted - 2003-08-28 : 15:20:47

I have a DTS package that transfers data from a SQL 2000 database to an Access 2000 database. I have created a process to loop through a table to pull the Access database location which is set to strFileLocation. The first time through the loop the connection is set properly but it never disconnects. How can I disconnect from the Access database?

Function Main()
Dim oPkg, oExecSQL, sSQLStatement
Dim conAccessDatabase

DTSGlobalVariables("strFileLocation").Value = "C:\Temp\" & DTSGlobalVariables("strFolderName").Value & "\Database.mdb"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent

Set conAccessDatabase = opkg.Connections("Customer")
conAccessDatabase.DataSource = DTSGlobalVariables("strFileLocation").Value

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Set conAccessDatabase = Nothing

Main = DTSTaskExecResult_Success

End Function

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-28 : 19:50:17

HAVE YOU CONSIDERED ACCESS DATA PROJECTS?

JuniorProgrammer
Starting Member

10 Posts

Posted - 2003-08-29 : 08:17:00

Not sure what that is but I am trying to do this same import process with a text file and the file location is not reconnecting during each loop.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-29 : 12:52:26

Your script seems OK and must work properly.
I think something wrong is with the loop.
What is that loop?

JuniorProgrammer
Starting Member

10 Posts

Posted - 2003-08-29 : 13:44:34

The loop tells the package to go back to the beginning of the process and execute the sql task to pull the strFolderName variable. Here is the code for the loop:


Function Main()
	dim pkg

	set pkg = DTSGlobalVariables.Parent
	set stpbegin = pkg.Steps("DTSStep_DTSExecuteSQLTask_1")
	
	stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

	DTSGlobalVariables("intClient").Value = 0
	DTSGlobalVariables("strFolderName").Value = ""
	DTSGlobalVariables("strFileLocation").Value = ""

	Main = DTSTaskExecResult_Success
End Function

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-31 : 17:42:44

Sorry, I still can't see how the loop is implemented.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-09-19 : 01:34:52

Don't transfer, connect.
Unless you need to work offline for some reason use access data project to connect.

In access create new project then connect to sql server.

Reports, Forms, Code in access but backed is linked into SQL Server and you can even develop views, sps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources