Edit ODBC settings

[Guest]

I built an app on my dev workstation. It uses a ProvideX ODBC driver with a
reference to the DB location, like this: T:\DBFolder. However, due to severe
network latency when using the ProvideX (file-based) ODBC connection, I want
to run this on the server, where the path is C:\Data\Apps\DBFolder.

That is, the DB is on a mapped drive relative to my dev workstation but
local to the server.

I created a DSN on the server identical in all respects to the one on my
workstation except for the required changes from mapped to local paths. How
can I now edit the path in the ODBC-linked table to reflect the local path in
the DSN? My app works on the server only so long as I have that same drive
mapped (back to a hard drive on the same server), which is obviously very
inefficient. I have only Access runtime on the server.

 

[Guest]

Let me try to be more simple. I need to edit the connection string from
T:\DBFolder to C:\Data\Apps\DBFolder so that it will work on the server.

How can I programmatically access the Linked Table Manager for ODBC? I
already use file-based RefreshLinks to re-link between FE/BE MDB's, but
cannot figure out how to refresh the ODBC links programmatically.

 

[Guest]

I have an app that does something similar, maybe it will help.
I have a template mdb that uses FoxPro ODBC links. I use the same mdb to
switch between similar groups of FoxPro tables in different directories.
Here is the routine that is called for each table whos link needs to be
changed/refreshed

The crux is to reset the TableDef.Connect string, then call
TableDef.RefreshLink
An example call to this sub:
LinkTable("FoxODBCconnect", "Layers", "Layers.dbf","c:\FoxTables\model2")

Public Sub LinkTable(odbcDSN As String, linkName As String, tblName As
String, path As String)
Dim dbs As Database, tdf As TableDef
Dim isTblDefPresent As Boolean

' Return reference to current database.
Set dbs = CurrentDb
' check if already present
On Error Resume Next
isTblDefPresent = True
Set tdf = dbs.TableDefs(linkName)
If (Err.Number > 0) Then
Err.Clear
isTblDefPresent = False
End If
On Error GoTo ErrorHandler

If Not isTblDefPresent Then
Set tdf = dbs.CreateTableDef(linkName)
tdf.SourceTableName = linkName
End If

'attempt a more flexible ODBC link
tdf.Connect = "ODBC;DSN=" + odbcDSN + ";SourceDB=" + path +
";;;;;;;;TABLE=" + linkName
' tdf.SourceTableName = linkName

If isTblDefPresent Then
tdf.RefreshLink
Else
dbs.TableDefs.Append tdf
End If
'MsgBox ("Table " + tblName + " successfully linked")


Exit Sub
ErrorHandler:
MsgBox ("unable to link table " + tblName + " " + Err.Description)
End Sub

 

[Van T. Dinh]

You may want to have a look at The Access Web article:

http://www.mvps.org/access/tables/tbl0012.htm

An alternative I like is the DSN-less link:

http://www.accessmvp.com/djsteel/DSNLessLinks.html

 

[Guest]

Thanks, Bruce.

I had something pretty close to that (although with only six tables, I was
simply hard-coding the new DSN for each), but found that it was failing
because my ProvideX ODBC connect string exceeds 255 characters when I use the
local path. The switch from T: to C:\Data\Apps put it over the edge. Here is
the original connect string:

ODBC;DSN=IFPMAS90;Description=IFP MAS
90;Directory=T:\MAS90;Prefix=T:\MAS90\SY\,
T:\MAS90\==\;ViewDLL=T:\MAS90\Home\;Company=IFP;LogFile=\PVXODBC.LOG;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer;TABLE=AR_InvoiceHistoryHeader

As you can see, the connect string includes all the parameters included in
the DSN. At this point, I suspect I'm just stuck unless I find out that some
part of the string is not necessary (and this is a ProvideX question, hardly
an Access question). Whoever invented the number 255, anyway?