Link table in code doesn't "take"
Ron Hinds
code snippet:
strConnect = "ODBC;Description=" & strDesc & ";DRIVER=SQL Server;SERVER=" &
gstrSQLServer & ";UID=sa;PWD=" & strPWD & ";APP=Microsoft Open Database
Connectivity;WSID=" & strWS & ";DATABASE=Prototype"
For Each tdf In db.TableDefs
If tdf.Attributes And dbAttachedODBC Then
tdf.Connect = strConnect
tdf.RefreshLink
db.TableDefs.Refresh
End If
Next
It doesn't happen for every table, but for some reason, for some of the
tables, the new Connect string doesn't "take"; I check it and it's still the
same as it was before. What causes this and what can I do about it?
Douglas J. Steele
connection string. The Connect property is read-only once the TableDef
object has been appended to the TableDefs collection.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:udM0Y8hM...@TK2MSFTNGP12.phx.gbl...
Ron Hinds
==============
Connect Property
-------------------------
Sets or returns a value that provides information about the source of an
open connection, an open database, a database used in a pass-through query,
or a linked table. For Database objects, new Connection objects, linked
tables, and TableDef objects not yet appended to a collection, this property
setting is read/write. For QueryDef objects and base tables, this property
is read-only.
==============
Read/Write for linked tables. This fits as I have been using the same method
with linked Access tables with no problem for quite some time.
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OsmyyXiM...@TK2MSFTNGP09.phx.gbl...
Douglas J. Steele
recreate it. That's the approach I've always used.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:OIMF2giM...@TK2MSFTNGP09.phx.gbl...
Ron Hinds
table of attached tables that has the necessary information in it? E.g.
sometimes the SourceTableName doesn't match the Name, etc. Or do you just do
it in code, saving the relevant information in local variables? Thanks!
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uqy9ymiM...@TK2MSFTNGP09.phx.gbl...
Pieter Wijnen
cnstr = "driver={SQL Server};server=myserver;" & _
"database=mydb;Username=<username>;PWD=<strong password>"
should suffice
Pieter
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:udM0Y8hM...@TK2MSFTNGP12.phx.gbl...
david epsom dot com dot au
2) Stays the same if there is an error
3) Looses the connect string if the TDF goes out of scope before the
Refresh.
What does your exception handling look like?
(david)
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:udM0Y8hM...@TK2MSFTNGP12.phx.gbl...
david epsom dot com dot au
DAO, unlike RDO, can connect to several different kinds of things,
not just to ODBC. DAO assumes that the first field is a type field:
the default blank value is for Jet databases. That is, if your string
does not start ";Database=" or "ODBC" or "TEXT" or whatever, you will
get an error message something like:
"driver=mydb" is not a recognised ISAM
(david)
"Pieter Wijnen" <mysurn...@online.no> wrote in message
news:aJZlc.153424$jf4.8...@news000.worldonline.dk...
Douglas J. Steele
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html
There are other (better) approaches. For instance, you could create a
collection and add to it, rather than having to dynamically redimension the
array.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:uo8Vt3iM...@TK2MSFTNGP10.phx.gbl...
Van T. Dinh
I think the Help topic is wrong. I have been using RefreshLink Method (in
A2K2) to change the Connection String / Connect Property on ODBC-linked
Tables without any problem.
--
HTH
Van T. Dinh
MVP (Access)
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OsmyyXiM...@TK2MSFTNGP09.phx.gbl...
Ron Hinds
manually. Then, in the Debug window I typed:
?CurrentDb.TableDefs("error_log").Connect
I used the Connect string returned as a template. I will try your suggestion
though which seems to dovetail nicely with Doug's (see earlier post). Is
this a so-called "DSN-less" connection string?
"Pieter Wijnen" <mysurn...@online.no> wrote in message
news:aJZlc.153424$jf4.8...@news000.worldonline.dk...
Ron Hinds
news:OhWThCnM...@TK2MSFTNGP12.phx.gbl...
> 1) Can't refresh to change from ODBC to MDB
They are already ODBC-linked tables - I just want to point them to a
different server.
> 2) Stays the same if there is an error
That would appear to be the problem. Unfortunately, no error is returned
(see response below).
> 3) Looses the connect string if the TDF goes out of scope before the
> Refresh.
Well I've tried to keep that code in the loop.
> What does your exception handling look like?
I'm checking the return codes for API calls and calling an API error
function for those. Otherwise I am using the standard VBA error
handling/reporting. Here is the entire function:
Public Function RefreshLinksODBC(DSNFile As String) As Boolean
On Error GoTo Error_RefreshLinksODBC
RefreshLinksODBC = False
Dim db As Database
Dim strError As String
Dim lRet As Long, lSize As Long, lErrCode As Long
Dim I As Integer, iSize As Integer
Dim strDesc As String
Dim strWS As String
Dim strPWD As String
Dim strConnect As String
Dim tdf As TableDef
Set db = DBEngine(0)(0)
'Initialize string variables to fixed length for API calls
gstrSQLServer = String(32, vbNullChar)
strDesc = String(32, vbNullChar)
strWS = String(32, vbNullChar)
lRet = SQLReadFileDSN(DSNFile, "ODBC", "SERVER", gstrSQLServer, 32,
lSize)
If lRet Then
If lSize = 0 Then
'Need to prompt for server name
DoCmd.openform "frmSQLServer"
If Len(gstrSQLServer) < 1 Then
msgbox "Must select a Database server!", vbCritical, "Hey
hammerhead!"
GoTo Exit_RefreshLinksODBC
End If
Else
gstrSQLServer = Left(gstrSQLServer, lSize)
End If
Else
GoTo ODBC_Error
End If
'Save new server name in DSN file
lRet = SQLWriteFileDSN(DSNFile, "ODBC", "SERVER", gstrSQLServer)
If lRet = 0 Then GoTo ODBC_Error
lRet = SQLReadFileDSN(DSNFile, "ODBC", "Description", strDesc, 32,
lSize)
If lRet Then
strDesc = Left(strDesc, lSize)
Else
GoTo ODBC_Error
End If
If InStr(1, strDesc, "Backup") > 0 Then
strPWD = "yyyyyyyy"
Else
strPWD = "xxxxxx"
End If
lSize = Len(strWS)
If GetComputerName(strWS, lSize) Then
strWS = Left(strWS, lSize)
Else
err.Raise GetLastError()
End If
strConnect = "ODBC;Description=" & strDesc & ";DRIVER=SQL
Server;SERVER=" & gstrSQLServer & ";UID=sa;PWD=" & strPWD & ";APP=Microsoft
Open Database Connectivity;WSID=" & strWS & ";DATABASE=Prototype"
For Each tdf In db.TableDefs
If tdf.Attributes And dbAttachedODBC Then
tdf.Connect = strConnect
tdf.RefreshLink
db.TableDefs.Refresh
End If
Next
'Set global connect string for PassThrough queries
gstrODBC = strConnect
RefreshLinksODBC = True
GoTo Exit_RefreshLinksODBC
ODBC_Error:
'ODBC error - enumerate
For I = 1 To 8
strError = String(255, vbNullChar)
lRet = SQLInstallerError(I, lErrCode, strError, 255, iSize)
strError = Left(strError, CLng(iSize))
If lErrCode > 0 Then
LogError "RefreshLinksODBC", lErrCode, strError
End If
Next
Exit_RefreshLinksODBC:
Exit Function
Error_RefreshLinksODBC:
Dim errX As error
For Each errX In Errors
LogError "RefreshLinksODBC", errX.Number, errX.Description
Next
Resume Exit_RefreshLinksODBC
End Function
david epsom dot com dot au
>If tdf.Attributes And dbAttachedODBC Then
because (a) it's not how I do it, and (b) the attributes are not independent
binary flags.
I check the connect string to see if it starts with "ODBC"
Also, I see in my code that I only refresh the tabledefs collection BEFORE
and AFTER the tdf loop, not during.
You might want to check inside the IF in the loop to see if you are actually
hitting all the ODBC tables.
There are also, I think, some situations in which the refresh really does
not work correctly or in the expected way. I do in fact mostly use
create/delete, although I have refresh as an option in my interface. I have
always been suspicious about refreshing ODBC links to point to a new server,
because (a) I don't do it that way, and (b) ODBC connections are cached by
the Jet engine.
But plenty of VB people do refresh from one ODBC connection to another.
Problem reports mostly have to do with IISAMs, DSN's or Server Alias's, not
(before now) with straight forward DSN'less ODBC connection strings.
(david)
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:uzNTfquM...@TK2MSFTNGP10.phx.gbl...
Joe Fallon
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .
Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables
Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String
If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If
SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."
Call DeleteODBCTableNames
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)
Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing
Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub
Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables
End Sub
'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames
Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I
dbs.Close
Set dbs = Nothing
Exit_DeleteODBCTableNames:
Exit Sub
Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames
End Sub
--
Joe Fallon
Access MVP
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:udM0Y8hM...@TK2MSFTNGP12.phx.gbl...
徐军
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
david epsom dot com dot au
2680 Set tbldef = dbRemote.CreateTableDef(sLinkName & "_",
nTDFAttribute, sRemote, sConnect)
2700 dbRemote.TableDefs.Append tbldef 'using modified name
2710 dbRemote.TableDefs.Delete sLinkName
2720 tbldef.Name = sLinkName 'change name to correct
name
(david)
"Joe Fallon" <jfal...@nospamtwcny.rr.com> wrote in message
news:%2337EB%23wMEH...@TK2MSFTNGP09.phx.gbl...
Terry Kreft
strConnect = "ODBC;Description=" & strDesc & ";DRIVER=SQL Server;SERVER=" &
gstrSQLServer & ";UID=sa;PWD=" & strPWD & ";APP=Microsoft Open Database
Connectivity;WSID=" & strWS & ";DATABASE=Prototype"
For Each tdf In db.TableDefs
with tdf
if len(.Connect)> 0 then
.Connect = strConnect
.RefreshLink
End If
end with
Next
I seem to remember (in the dim distant past) that testing the Attribute as
you are doing can fail which is why I test whether the table has a
connection string.
--
Terry Kreft
MVP Microsoft Access
"Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
news:udM0Y8hM...@TK2MSFTNGP12.phx.gbl...
Terry Kreft
--
Terry Kreft
MVP Microsoft Access
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OsmyyXiM...@TK2MSFTNGP09.phx.gbl...
Gary Walter
but I was wondering if it was possible that
including the Refresh within the loop might
not be "reordering" the TableDefs.....
it would be interesting to see what you get
if you debug printed the table name in the loop,
with and without the Refresh.
> Also, I see in my code that I only refresh the tabledefs collection BEFORE
> and AFTER the tdf loop, not during.
>
>
> > For Each tdf In db.TableDefs
Debug.Print "All tables: " & tdf.Name
> > If tdf.Attributes And dbAttachedODBC Then
Debug.Print " Change Me table: " & tdf.Name
Ron Hinds
and see what works best for me.
"Terry Kreft" <terry...@mps.co.uk> wrote in message
news:g8adnTNpMLd...@karoo.co.uk...