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:
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"
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
If td.Name = stLocalTableName Then
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stLocalTableName, stConnect)
CurrentDb.TableDefs.Append td 'Crashes at this line
If x = 0 Then fnAttachNewTables
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.