Automatically reattach SQL backend tables

[Proko]

I am currently converting by Access backend to SQL Server. However the
problem I am having concerns the front end, still in Access.
When my main form opens it triggers some code that checks that the data file
exists. If it cannot locate the data file it asks the user for the path to
the data file. How would I change the code to make it work for a SQL
backend. Any help would be greatly appreciated.Currently the code looks like
this:

Global Const gstr_DatabaseName = “MyAccessData.mdbâ€
………………………………………………………………..
'Purpose: To check that all table attachments are valid. If not then try
' to reattach each table. Ask the user where the file Database is
located.
' If any reattachment cannot be made then abort program.
'
'1. Count the number of attached tables.
'2. For each attached table do
'2.1 Check the attachment
'2.2 Reattach if required
'3. If any reattachments could not be made then quit program.
'
'
Sub AE_B_CheckAttachments()


Dim db As Database
Dim i As Integer
Dim intErr As Integer
Dim intTableCount As Integer
Dim RetVal As Variant
Dim rst As Recordset
Dim strFileName As String
Dim strmsg As String
Dim strSearchPath As String
Dim strTemp As String
Dim strUserSpecifiedDir As String
Dim tbl As TableDef

On Error GoTo AE_B_CheckAttachments_ERR

Set db = CurrentDb()

'-----------------------------------------------------------------------------------------------
'1. Count the number of attached tables
'-----------------------------------------------------------------------------------------------
For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If tbl.Connect <> "" Then
intTableCount = intTableCount + 1
End If
Next i
'set up status meter
RetVal = SysCmd(SYSCMD_INITMETER, "Attaching tables", intTableCount)
intTableCount =
'-----------------------------------------------------------------------------------------------
'2. For each attached table d
'-----------------------------------------------------------------------------------------------
For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If tbl.Connect <> "" Then
On Error Resume Next
'2.1 Check the attachment
Set rst = tbl.OpenRecordset()
intErr = Err
On Error GoTo AE_B_CheckAttachments_ERR
If intErr Then
'2.2 Reattach if required
GoSub AE_B_CheckAttachments_AttachTable
Else
rst.Close
End If
intTableCount = intTableCount + 1
RetVal = SysCmd(SYSCMD_UPDATEMETER, intTableCount)
End If
Next i

RetVal = SysCmd(SYSCMD_REMOVEMETER)

Exit Sub

'------------------------------------------------------------------------------------------------
AE_B_CheckAttachments_AttachTable
'------------------------------------------------------------------------------------------------
'try the directory previously specified by the user (if they have
already done so).
If Len(strUserSpecifiedDir) = 0 Then
Call AE_B_AskTheUser(strUserSpecifiedDir)
End If
If Len(strUserSpecifiedDir) > 0 Then
tbl.Connect = ";DATABASE=" & strUserSpecifiedDir
On Error Resume Next
tbl.RefreshLink
intErr = Err
On Error GoTo AE_B_CheckAttachments_ERR
If intErr = 0 Then
'Reattach was successful
Return
End If
End If

'reattach has failed.
On Error Resume Next 'dont want to be interrupted now!
strmsg = "The file called '" & gstrDATABASE_NAME & "' could not be
found. "
strmsg = strmsg & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)
strmsg = strmsg & "This system will now shutdown and return you to
Windows."
Beep
MsgBox strmsg, MB_ICONSTOP, "System StartUp Failed"
db.Close
Application.Quit

Return
'------------------------------------------------------------------------------------------------
AE_B_CheckAttachments_ERR:
'------------------------------------------------------------------------------------------------
Call Error_Handler(True)
Exit Sub
End Sub

___________________________________________

Sub AskTheUser (pstrPath as String)

Dim strmsg as String
Dim strFilePath as String

strmsg = “The system has been unable to locate the database. Please select
theâ€
strmsg = strmsg & “file from the dialogue box that followsâ€
MsgBox strmsg, MB_ICONEXCLAMATION, “File not foundâ€

strFilePath = szFileOpenDlg(“â€, “*.MDBâ€)
if strFilePath = Ҡthen
pstrPath = “â€
Else
pstrPath = strFilePath
End if
End Sub

 

[Douglas J. Steele]

If you're using a DSN, see http://www.mvps.org/access/tables/tbl0010.htm at
"The Access Web"

If you're going DSN-less, see
http://www.accessmvp.com/DJSteele/DSNLessLinks.html