Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Error 4060 When appending tables in code

    Hello all. Never seen this. I am using a snippett of code I have used numerous times to programmatically attach tables in SQL Server and avoid the headaches of dsn maintenence.

    The following code runs great but then crashes on the .append method. The message seems to a sql generated message - and indicates I may not have permissions to to attach to these tables remotely - how else does one attach to tables other than remotely? (kidding)

    here is the code:
    Function AttachDSNLessTable()
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef, rsttables As Recordset
    Dim stConnect As String, stServer As String, stdatabase As String, stUsername As Integer, _
    stLocalTableName As String, stRemoteTableName As String, stPassword As Integer, x As Integer
    stServer = DLookup("[servername]", "[tblDSNLESSDetails]")
    stdatabase = DLookup("[databasename]", "[tblDSNLESSDetails]")
    stuser = DLookup("[username]", "[tblDSNLESSDetails]")
    x = 0
    Set rsttables = CurrentDb.OpenRecordset("Select * from tblDSNLessTableNames", dbOpenDynaset)
    stConnect = "ODBC;DRIVER=SQL Server Native Client 10.0 ;SERVER=" & stServer & ";DATABASE=" & stdatabase & ";Trusted_Connection=Yes"
    rsttables.MoveFirst
    If Not rsttables.EOF Then
    Do Until rsttables.EOF
    stLocalTableName = rsttables!tblTableName
    For Each td In CurrentDb.TableDefs
    If Left(td.Name, 4) = "Msys" Then 'GoTo Line33
    Else
    If td.Name = stLocalTableName Then
    CurrentDb.TableDefs.Delete stLocalTableName
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stLocalTableName, stConnect)
    CurrentDb.TableDefs.Append td 'Crashes at this line
    End If
    End If
    Next
    rsttables.MoveNext
    Loop
    End If
    If x = 0 Then fnAttachNewTables
    Exit Function

    AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

    End Function
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There can be several causes to this error:
    1. The connection string (stConnect) is incorrect.
    2. You do not have the necessary permissions (as defined at the SQL server side) to open the table (or to access the database).
    3. There are already too many open connections and the server does not accept any new.

    Check the Application event log file of the server as well as the log file of SQL Server, there could be an indication there to help you to understand what happens. I would first double check the connection string, though.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •