Automating import table via ODBC

[4281967]

Daily I have to pull data for a report into an Access database via an ODBC connection. The problem is - I have to start a Citrix connection to a server in another city (where the dsn resides) then open the mdb file on my local PC. Then I do the "File-Import" and select ODBC - then I can select from the dsn's on the server. Once I do that, I can grab my table. I tried to view the table properties so that I could start working on a connection string - but since it's a Citrix connection, every time I try to view the properties, it kills my session.

Any ideas or suggestions?

 

[PHV]

Have you tried to play with a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

no - since it's production data I have been nervous about doing that - (my report has to "mangle" a lot of the data to get revenue projections.) however, I'll give it a try.

I am assuming once I get my connection string correct, it will work the same in a Citrix session.

 

[cmmrfrds]

The Citrix server is providing you a remote desktop on the server. This simulates as if the remote mdb is local, but it is not. You can probably copy and paste to your local PC or FTP the data, but it unlikely that you will be able to make an ODBC connection from your local PC. Check with the IT person at your site.

 

[4281967]

Are you saying I would or would not be able to automate the ODBC connection while in a Citrix session?

 

[cmmrfrds]

On the remote PC (Citrix Server) you can make an ODBC connection to the remote database(which I assume is on the LAN at the remote site), but not from the local PC. Are you trying to make the connection through code on the remote PC?

 

[4281967]

Here's what I do (and want to do)
I currently start a Citrix connection to a server in another city, and then (via the Citrix session desktop)open the mdb file on my local PC. Currently I manually perform the "File-Import", select ODBC, and grab the table.

What I would like to do is open a Citrix connection, open the mdb and press a button on a form which would automate the table import process.

 

[cmmrfrds]

Search for dsn-less connection for examples of how to link through code.

Here is an example.

Public Function CreateLinkedTable()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection

' Create the new Table.
tbl.Name = "Linked_Employees"
Set tbl.ParentCatalog = cat

' Set the properties to create the link.
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
tbl.Properties("Jet OLEDB:Create Link") = True
' Append the table to the Tables collection.
cat.Tables.Append tbl
Set cat = Nothing
End Function