Code works in A2k but NOT in A2K3

[supmktg]

The following code creates a new mdb and adds backup tables from linked SQL Server tables from the currentdb. This code works flawlessly in Access 2000, but seems to fail at the line ".Execute strSQL, dbFailOnError" in Access 2003.

Private Sub cmdBackup_Click()

   'Back up ALL linked tables
    Me.lblProgress.Caption = "Backing up SQL Data..."
    Dim strFileName As String
    Dim strSQL As String
    Dim tdf As DAO.TableDef
    
    'Create the destination fileNAME
    strFileName = Me.txtSaveBackupFile & "/DataBackup" & "_" & Format(Date, "yyyymmdd") & ".mdb"     
    
    'Create the destination database
    DBEngine.CreateDatabase strFileName, dbLangGeneral
    
    'Transfer the data
    With CurrentDb
               
        For Each tdf In .TableDefs
        
            'Select only tables with connection strings, ignore system tables & views
            If Not IsNull(tdf.Connect) And Not tdf.Name Like "*Sys*" And Not tdf.Name Like "vi_*" Then
                
            'Update progress as we move through the tables
            Me.lblProgress.Caption = "This may take a few minutes!" & vbCrLf & "Backing up SQL Data " & tdf.Name & "..."
            Me.Repaint
            DoEvents
            
                'SQL to make each table
                strSQL = "SELECT *" & _
                         " INTO [" & tdf.Name & "]" & _
                         " IN '" & strFileName & "'" & _
                         " FROM [" & tdf.Name & "]"
						 
                'Execute the SQL statement
                .Execute strSQL, dbFailOnError
                                
            End If
        
        Next tdf
    End With
       'Acknowledge completion
       Me.lblProgress.Caption = "Backup Complete!"
   
End Sub

Can anyone help me figure out why?

Thanks,
Sup

 

[ajetrumpet]

Sup,

I can say that MS has a reputation of making very small syntax changes from version to version and not telling anyone about them. It makes sense to say that your error is a result of invalid syntax, like incorrect bracketing or incorrect use of quotation marks. I am no expert in the changes that are made between the versions, but I would first try modifying that SQL string you have.

My guess would be that there is a small problem with the characters you're using, weather they be brackets, or whatever...

 

[Moniker]

More importantly, what is the error message?

 

[supmktg]

I removed the brackets and switched to DoCmd.RunSQL instead of .execute .
Now it runs fine in both 2000 & 2003.

Thank you very much for your help!
Sup