Private Sub cmd_ConvertLinkedToLocal_Click()
' For rare instances in which the linked table found no longer exists in SQL Server
On Error Resume Next
‘ Get current database path/name
myDBName = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1, 200)
‘ Check whether still original name and if so, instruct user to change the database name
If myDBName Like "AuditDatabase*" Then
MsgBox _
"AuditDatabase' is included at the beginning of the database " & _
"name." & vbCrLf & vbCrLf & "Please rename your local database before " & _
"proceeding." & vbCrLf & _
"(e.g., change 'AuditDatabase' to 'myAuditDatabase')": Exit Sub
End If
‘ Make sure user really wants to convert database.
msgboxA = MsgBox("DO NOT DO THIS UNLESS YOU ARE SURE!!!. " & vbCrLf & vbCrLf & _
"This will convert your linked tables to local tables!" & vbCrLf & vbCrLf & _
"ARE YOU SURE?!", vbExclamation + vbCritical + vbYesNo, "ARE YOU SURE?!")
' If no, do not proceed
If msgboxA = vbNo Then Exit Sub
' Capture beginning time
Me.txt_StartTime = Now()
Dim myDB As Database, myTDF As TableDef
Set myDB = CurrentDb
' Check each table definition
For Each myTDF In myDB.TableDefs
' If a linked table (source is outside of the current db) . . .
If myTDF.SourceTableName <> "" Then
' Select the table and convert to local
DoCmd.SelectObject acTable, myTDF.Name, True
RunCommand acCmdConvertLinkedTableToLocal
End If
Next
' Capture ending time - note we also have a text box that computes the difference between beginning time and ending time so we no how long it took (surprisingly quick)
Me.txt_FinishTime = Now()
' Let user know the process is completed
MsgBox "Conversion Completed"
End Sub
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: