Using Access event code in DTS

[HamotIntern]

I have a piece of code that is associated to a button on a simple form in Access. When I press it, it looks at a table for which tables to import from an AllBase database and then imports it/them. DTS’s Import Function does not work in this application because it doesn’t work with the ODBC Driver we are using. However, Access’ TransferDatabase function does work and works well. Now, we would like to automate the whole process of importing the tables, scrubbing them, inserting the data into existing tables, and then generating a daily report for each patient in the ICU. Below is the code, is there a way to use it in the DTS or what would be an equivalent method to do this?

Dim rst As ADODB.Recordset
Dim strCriteria As String

Set rst = New ADODB.Recordset
strCriteria = "Import = True"

With rst
.Open "bxj_Import", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
.Find strCriteria
Do While Not .EOF
Label3.Caption = rst!TblName
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=CareVue;UID=xyz;PWD=xyz", acTable, rst!TblName, rst!NewTblName
.Find strCriteria, 1
Loop
End With

rst.Close
Set rst = Nothing

An important note would be that this is from an adp file that is attached to the SQL server.

 

[Meleagant]

All you want to do is import a table into Sql? You can just drag your two databases and define a transformation between them. Define one connection for the access DB and the other to the AllBase DB. Then you should be able to select the tables you want to export from Access to AllBase in the properties of the data transformation. Let me know if this is what you need...

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[HamotIntern]

It’s not quite that easy (I tried that first). MS Access is able to do something SQL Server can’t, and that’s why I was using it. The database that the adp file is attached to is SQL server, not an Access database. So I’m essentially working with the SQL server with Access as the front end. This has worked OK for development but now I need to automate the task. The task being,
• Open Access
• Open form
• Click button (and execute the above code.)
• Close Access
If Access could run automated tasks like that, it would work OK. What I would REALLY like to do is leave Access behind and do the same thing in SQL server using script (not the Transform Data Task because it gives me the following error when I try. “Package Error
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description : ODBC driver does not support the requested properties.”)
I hope this helps clarify things for you.

 

[HamotIntern]

One of my co-workers gave me a hand writing the script. We think we’re on the right track and may even have it, but are receiving an error that we cannot figure out. The code we wrote is,

Function Main()

Dim sServer
Dim sLogin
Dim sPwd
Dim oCn
Dim rst
Dim sNames
Dim oFso
Dim oFileOut
Dim sLineOut
Dim appADP


' Create the ADO Connection and Recordset objects.
Set oCn = CreateObject( "ADODB.Connection" )
Set rst = CreateObject( "ADODB.Recordset" )
Set appADP = CreateObject( "Access.Application" )

' Create a FileSystemObject.
Set oFso = CreateObject("Scripting.FileSystemObject")

' Set the conneciton string, open the connection and get rows.
oCn.ConnectionString = "Driver={SQL Server}; Server=IMDSQLRPT\TEST; DATABASE=CareVueSQLUpsized; UID=reports; PWD=reports;"
oCn.open
rst.Open "SELECT TblName, NewTblName, Import, Scrub FROM bxj_Import WHERE Import = 1", oCn

' Read through all the rows.
While Not rst.EOF
' Get the column data for each column in the row.
'If rst!Scrub = 1 Then
'appADP.DoCmd.TransferDatabase 0, "ODBC", "ODBC;DSN=CareVue;UID=ODBC;PWD=ODBC", 0, rst!TblName, "tmp_" + rst.NewTblName
'appADP.Application.CloseCurrentDatabase

'Else
msgbox("Hi")
appADP.DoCmd.TransferDatabase 0, "ODBC", "ODBC;DSN=CareVue;UID=ODBC;PWD=ODBC", 0, rst.TblName, rst.NewTblName

' end If

rst.MoveNext
Wend
' We're finished-close the database objects.
rst.Close
Set rst = Nothing

Main = DTSTaskExecResult_Success
End Function


The error that we receive on line 41 (appADP.DoCmd.TransferDatabase 0, "ODBC", "ODBC;DSN=CareVue;UID=ODBC;PWD=ODBC", 0, rst.TblName, rst.NewTblName) is “Object doesn’t support this property or method: ‘rst.TblName”

If we exchange the ‘.’ for ‘!’ (appADP.DoCmd.TransferDatabase 0, "ODBC", "ODBC;DSN=CareVue;UID=ODBC;PWD=ODBC", 0, rst!TblName, rst.New!TblName), we get “Expected end of statement”.

It has to be something stupid, but for the life of us, we can’t figure it out.

 

[Meleagant]

What about a DSNless connnection string like this:
DoCmd.TransferDatabase acLink, "ODBC", _
"ODBC;DRIVER=SQL Server;" & _
"UID=UserId;" & _
"DATABASE=DBName;" & _
"SERVER=DBServer;" & _
"PWD=Password", acTable, TableName, TableName, False, True

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[Meleagant]

Here is a snippet from a Active X Script that I wrote, maybe this will help.

Function Main()

    Dim oPkg, oDataPump, sSQLStatement
    Dim objCon
    Dim objCmd
    Dim objRsPackageNames

    Set objCon = CreateObject("ADODB.Connection")
    objCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDBName;Data Source=MyDBServerName"
    objCon.CursorLocation = 3  'adUseClient
    objCon.Open

    Set objCmd = CreateObject("ADODB.Command")
    objCmd.ActiveConnection = objCon
    objCmd.CommandType = 1  'adCmdText

    ' Build SQL Statement
    sSQLStatement = "Update Invoice Set Status = 9 Where InvoiceGroup = " & DTSGlobalVariables("gInvoiceGroupId").Value
    objCmd.CommandText = sSQLStatement
    Set objRsPackageNames = CreateObject("ADODB.Recordset")
    Set objRsPackageNames = objCmd.Execute
    
    ' Clean Up
    Set objCmd = Nothing
    Set objCon = Nothing
    Set oDataPump = Nothing
    Set oPkg = Nothing
    Set objCmd2 = Nothing
    Set objRsPackageNames = Nothing

    Main = DTSTaskExecResult_Success

End Function

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[HamotIntern]

I'm slowly getting it on my own. The part of the code that is giving me grief right now is,

With rst
.Source="bxj_Import"
.ActiveConnection=oCn
.CursorType=adOpenKeyset
.Open
.Find strCriteria
Do While Not .EOF
...

Specifically the line ".CursorType=adOpenKeyset". Since I want to use .find, the cursor type cannot remain the default (adOpenForwardOnly). The thing is that DTS does not recognize what adOpenKeyset is. I’m pretty sure that this is the correct syntax and so I’m not sure how to make DTS know what adOpenKeyset is. Two sources that I have used to get to this point are,

http://msdn.microsoft.com/library/d...us/odeopg/html/deovrrecordsetobjectbasics.asp

and the topic “Using Cursors with ADO” in the DTS help.