I'm importing data from an Excel file into a Database, and I'm using a
Data Link to state the connection properties of the database.
However, I do a lot of transformations on the data along the way, in
ActiveX script, and I need to run some queries on the database in this
I used to have "Server" and "Database" variables provided by the user,
and then use them as follows:
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open = "Provider=SQLOLEDB.1; Data Source="+Server+"; Initial
Catalog="+DB+"; Integrated Security=SSPI"
SQLText = "Select CustID from tCustomer Where CustName =
rs.Open SQLText, conn
But now that I have a Data Link to hold those values, I now longer
need them passed in from the user - but I still need to know how to
execute this query on the database, inside the ActiveX script.
I know I can reference the Data Link connection like so:
Set pkg = DTSGlobalVariables.parent
Set udl = pkg.Connections("Microsoft Data Link")
But how do I use that to open a connection to the database, run a
query and return a recordset, during my transformation???
Any help would be appreciated - I haven't found any articles on this!
6. Access 2000