Problem creating a linked table via VBA

[kjv1611]

MS Office Access 2003
MS Windows XP Pro, SP2, etc.
No errors on this one at all, but doesn't do anything that I can see..

I am attempting to create a linked table to a SQL SERVER table via VBA. Here is my code:

Private Sub Form_Load()
On Error GoTo ErrHandle

    Dim strSQL
    Dim cn As New ADODB.Connection
    Dim connString As String
    Dim strUserID As String
    Dim recordsaffected As Long
    Dim tdf As TableDef
    
    connString = "Provider=MSDASQL.1;" & _
                      "Persist Security Info=False;" & _
                      "Data Source=MyDatabaseName"
    cn.ConnectionString = connString
    cn.Open connString
    cn.CommandTimeout = 300
    
    strSQL = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " & _
                "SET NOCOUNT OFF " & _
                "SELECT dbo.sysobjects.name AS 'TableName', dbo.sysobjects.id AS 'sysID', dbo.syscolumns.name AS 'ColumnName', sysusers.name AS 'Owner', sysusers.uid As 'UserID' " & _
                "INTO Sandbox.[MYDOMAIN\" & Environ("UserName") & "].SandboxTables " & _
                "FROM dbo.sysobjects INNER JOIN " & _
                "        dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN " & _
                "        dbo.sysusers ON dbo.sysobjects.uid = sysusers.uid " & _
                "ORDER BY 5, 4, 1"
    
    cn.Execute strSQL, recordsaffected
[HIGHLIGHT]    Set tdf = CurrentDb.CreateTableDef("Sandbox", , "Sandbox.[SHERMAN\" & Environ("UserName") & "].SandboxTables", connString)[/HIGHLIGHT]

Exit Sub

ErrHandle:
    MsgBox "An Error Has Occurred!" & _
                Chr(13) & Chr(13) & "Error Number" & Chr(9) & "Error Description" & Chr(13) & _
                Err.Number & Chr(9) & Chr(9) & Err.Description, vbCritical, "Error!"

End Sub

The highlighed code is the line that is not working. Everything else works exactly correct, no problems.

Thanks for any pointers/advice.

 

[rjoubert]

Check out DoCmd.TransferDatabase, using acLink as the first parameter. You'll have to set a few more parameters to connect to your DB.

 

[kjv1611]

Hmmm, that seems like it would be shorter than another method I found in the help file ("Connect and ReturnsRecords Properties Example (Client/Server)". I'll give it a shot.

Thanks.

 

[kjv1611]

Ok, I tried that method, and trying the connString already setup earlier, I'm getting this error message, now:

3170 Could not find installable ISAM.

Here is the exact code I used (just the portion for this operation - the rest is already posted, above):

DoCmd.TransferDatabase acImport, "ODBC Database", _
            connString, acTable, "Sandbox.[SHERMAN\sferguson].SandboxTables", "Sandbox"

Any suggestions?

 

[rjoubert]

Check out this support article from Microsoft...

http://support.microsoft.com/kb/209805

 

[kjv1611]

I may not be able to use that option, then, as it's my work pc, and the registry editor is disabled for non-IT support logins. I'll look at it, nonetheless, and post back with what I end up going with.

Thanks!