How does vba programmatically changing a linked table from a network to a local database affect when the append query is set to go to another database?
All please note the following query, is showing it will append to another database on the N:\. Will it do this despite having ran a script that links to another database on the C:\drive? In fact the following code is listed below.... so I know for sure they are no longer linked.
But do I also have to go in and change all the links in append queries also? If so, how do I change the below script to include queries and not just tables?
But do I also have to go in and change all the links in append queries also? If so, how do I change the below script to include queries and not just tables?
Public Sub ConnectLinkedTables(Optional bLinkLocally As Boolean)
On Error GoTo Err_Proc
Dim bError As Boolean
'
Dim dDateModifiedFrom As Date
Dim dDateModifiedTo As Date
Dim sMessage As String
Dim bNoErrors As Boolean
Dim tdf As DAO.TableDef
Dim sLinkConn As String
Dim sLinkPath As String
Dim sPathLink As String
Dim strNewconnect As String
Dim pfsBU As Object
Dim f As Object
Dim sAdjName As String
Dim pfs As Object
Dim sSQL As String
Dim i As Integer
Dim sLocalVersionOfFile As String
Dim sLocalPathOfFileOnly As String
Dim sNetworkVersionOfFile As String
Dim bFileExists As Boolean
Dim lValue As Long
Dim bAttempt2Fix As Boolean
Dim sUser As String
Dim lNotLinked As Long
Dim sTableNotLinked As String
sUser = GetUserName
'1st process
'should identify network mappings
'and put in a table with their perspective
'c drive counterparts.
'so clear temp table
'identify if here
'then run
'if here
'fill it up
'link from net to local
'then when asked to put back
'use the table to determine
'what links where
If TableExists("_tAccessLinks") = False Then
Call MYMESSAGEBOX("table: _tAccessLinks is missing for this feature")
GoTo Exit_Proc
End If
Call CloseAllMainCustomerForms
lValue = Application.GetOption("Error Trapping")
Call Application.SetOption("Error Trapping", 2)
If bLinkLocally = True Then
'first build table of links
For Each tdf In CurrentDb.TableDefs
DoEvents
If Not Left(tdf.NAME, 4) = "MSys" Then
If Left(tdf.Connect, 10) = ";DATABASE=" Then
'
sLinkConn = tdf.Connect
'
sPathLink = Right(sLinkConn, Len(sLinkConn) - 10)
sLocalVersionOfFile = LOCALVERSIONOFFILE(sPathLink)
sLocalVersionOfFile = Replace(sLocalVersionOfFile, "C:\_MY_DOCUMENTS_\", GetMyDocuments)
sLocalPathOfFileOnly = GetFolderPathOnly(sLocalVersionOfFile)
Set pfsBU = CreateObject("Scripting.FileSystemObject")
If pfsBU.FileExists(sPathLink) = True Then
Set pfsBU = CreateObject("Scripting.FileSystemObject")
Set f = pfsBU.GetFile(sPathLink)
dDateModifiedFrom = f.DateLastModified
Set f = Nothing
End If
If pfsBU.FileExists(sLocalVersionOfFile) = True Then
Set f = pfsBU.GetFile(sLocalVersionOfFile)
dDateModifiedTo = f.DateLastModified
Set f = Nothing
End If
Set pfsBU = Nothing
'Debug.Print sLinkConn
If Left(sPathLink, 2) <> "C:" And Left(sLinkConn, 3) <> "SQL" Then
'ensure local file exists
bFileExists = FileExists(sLocalVersionOfFile)
'If bFileExists = False Or dDateModifiedFrom <> dDateModifiedTo Then
If bFileExists = False Then
Call SendMsg("copying local file to: " & sLocalVersionOfFile)
Call MakeFolders(sLocalPathOfFileOnly)
Call CopyFile(sPathLink, sLocalVersionOfFile)
End If
'
If bFileExists = True Then
'now link to that new file path.
Call SendMsg("connecting table: " & tdf.NAME & " to: " & sLocalVersionOfFile)
If NetworkLinkConn(tdf.NAME) = "" Then
sSQL = "INSERT INTO [_tAccessLinks] ([LinkLocal], [LinkNet], [LinkTableName]) VALUES ('" & sLocalVersionOfFile & "', '" & sPathLink & "', '" & tdf.NAME & "')"
CurrentDb.Execute sSQL
DoEvents
End If
tdf.Connect = ";DATABASE=" & sLocalVersionOfFile
tdf.RefreshLink
Else
'need a warning that sql link cannot be linked locally.
End If
End If
End If
End If
Next
End If
If bLinkLocally = False Then
For Each tdf In CurrentDb.TableDefs
DoEvents
If Not Left(tdf.NAME, 4) = "MSys" Then
If Left(tdf.Connect, 10) = ";DATABASE=" Then
'
sLinkConn = tdf.Connect
sPathLink = Right(sLinkConn, Len(sLinkConn) - 10)
sLocalVersionOfFile = sPathLink
If Left(sPathLink, 2) = "C:" And Left(sPathLink, 3) <> "SQL" Then
'ensure local file exists
'now link to that new file path.
sNetworkVersionOfFile = NetworkLinkConn(tdf.NAME)
Call SendMsg("connecting table: " & tdf.NAME & " to: " & sNetworkVersionOfFile)
tdf.Connect = ";DATABASE=" & sNetworkVersionOfFile
tdf.RefreshLink
sSQL = "DELETE FROM [_tAccessLinks] WHERE [LinkTableName] = '" & tdf.NAME & "'"
CurrentDb.Execute sSQL
DoEvents
End If
End If
End If
Next
End If
bNoErrors = True
Exit_Proc:
Call ClearMsg
If bNoErrors = True Then
sMessage = "Successfully "
Else
sMessage = "Unsucessfully "
End If
sMessage = sMessage & " linked: "
If bLinkLocally = False Then
sMessage = sMessage & " network files!"
Else
sMessage = sMessage & " local files!"
End If
If lNotLinked > 0 Then
sMessage = sMessage & vbNewLine & vbNewLine & " with the exception of "
If lNotLinked > 1 Then
sMessage = sMessage & lNotLinked & " linked tables."
Else
sMessage = sMessage & lNotLinked & " linked table: (" & sTableNotLinked & ")"
End If
End If
Call MYMESSAGEBOX(sMessage)
On Error Resume Next
Call RECORD_ACTION("ConnectLinkedTables: -bLinkLocally: " & bLinkLocally & " -bNoErrors: " & bNoErrors)
Call Application.SetOption("Error Trapping", lValue)
Exit Sub
Err_Proc:
sTableNotLinked = tdf.NAME
If Err = 3011 And bAttempt2Fix = False Then
'then table doesn't exist in the local copy...
'must copy over the latest one..
lNotLinked = lNotLinked + 1
Call CopyFile(sPathLink, sLocalVersionOfFile)
Call SendEmail(DEVADMIN_EMAIL, "link locally: (" & bLinkLocally & ") did not completely work for user: " & sUser, "could not link table: (" & sTableNotLinked & ") within database: (" & CurrentDb.NAME & ")")
bAttempt2Fix = True
Err.Clear
Resume Next
Else
Call SendEmail(DEVADMIN_EMAIL, "link locally: " & bLinkLocally & " not working or user: " & sUser, "could not link table: " & tdf.NAME & " within database: " & sTableNotLinked)
End If
bError = True
Call LogError(Err, Err.Description, "_mCommon @ ConnectLinkedTables")
Resume Exit_Proc
End Sub
Last Comment
Gustav Brock
As long as the names of the tables (and perhaps queries) used in the code remain, the append query will work.
ASKER
I know it will work... what I'm concerned about is if its still going to update the network tables due to the fact it's explicitly stated in that query even though all the tables are linked to a c:\drive local copy.
The WHOLE reason why I'm linking to the local tables is so I don't disrupt the network tables in order to test before production! I don't want to mess up my production data on the network and can do whatever when the tables are linked locally on the c:\drive.
The WHOLE reason why I'm linking to the local tables is so I don't disrupt the network tables in order to test before production! I don't want to mess up my production data on the network and can do whatever when the tables are linked locally on the c:\drive.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Man, this is exactly what I need sir. Appreciate the response. Thankfully I'm up at 2:44 a.m. this morning. lol
Reading the documentation doesn't make clear to me the syntax between adding single-record append query and multiple-record append records. And is adding a single record just for test purposes then? Why the difference or why make that distinction?
Reading the documentation doesn't make clear to me the syntax between adding single-record append query and multiple-record append records. And is adding a single record just for test purposes then? Why the difference or why make that distinction?
I think you need some sleep. It doesn't matter if you insert one or many records, what matters is where.
If you are not sure where "where" is, you must check the append queries and - if created dynamically - the code the generates these.
If you are not sure where "where" is, you must check the append queries and - if created dynamically - the code the generates these.
ASKER
lol. You right.... "It doesn't matter if you insert one or many records, what matters is where " again that's the first thought in my head when reading the documentation. why try to separate it? and yes been staying up too late and not getting enough sleep.
A 'Single-record' append in the documentation is really referring to the situation where the appended values are specified as part of the query Value1, Value2 etc rather than being sourced from other tables. In this situation it is only possible to append a single record. Of course the 'Multiple-record append query' can append a single record as well depending on criteria, source table contents etc.
In the 'single record' case, Microsoft have chosen not to allow the externaldatabase option to be used (at least according to the documentation). The whys and wherefores are a mystery, only known to the elders of Kashmir.
In the 'single record' case, Microsoft have chosen not to allow the externaldatabase option to be used (at least according to the documentation). The whys and wherefores are a mystery, only known to the elders of Kashmir.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
