DoCmd works in Immediate window, not in Sub (1 Viewer)

sportsguy

Finance wiz, Access hack
Local time
Today, 04:38
Joined
Dec 28, 2004
Messages
358
Why does the DoCmd.TransferDatabase work in the Immediate window, but not in the Sub, which has always worked in the past? :confused:

Code:
Public tblname As String
Public tblNewname As Variant
Public pstrDatabasePath As String
Public Const dbType As String = "Microsoft Access"

Public Sub AttachTable(ByVal pstrDatabasePath, ByVal tblname, Optional ByVal tblNewname)

On Error GoTo Attach_Err
Debug.Print dbType
Debug.Print pstrDatabasePath
Debug.Print tblname


If IsNull(tblNewname) Then tblNewname = tblname

With DoCmd
    .DeleteObject acTable, tblname
    .DeleteObject acTable, tblNewname
    .TransferDatabase acLink, dbType, pstrDatabasePath, acTable, tblname, tblNewname
End With


Exit Sub

Attach_Err:
    
    If Err.Number = 7874 Then
        Debug.Print Err.Number & "  " & Err.Description & " when deleting table from local database - Macro continues"
        Resume Next
    ElseIf Err.Number = 3011 Then
        MsgBox "Wrong Access database!  Please select the correct Access database!", vbCritical, "FP&A"
    Else
        MsgBox Err.Number & "  " & Err.Description & "  " & tblname, vbCritical, "FP&A"
    End If


End Sub



Debug.Print
Microsoft Access
\\celerradc2\csdisaster$\Scorecard\cp.accdb
PORTAL


The following works in the IMMEDIATE window:
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\celerradc2\csdisaster$\Scorecard\cp.accdb", acTable, "PORTAL", "PORTAL"


just very confused why this subroutine doesn't work as it always has in the past. . . and works in the immediate window. .


ERROR MESSAGE:
3125 '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

any suggestions?

thanks in advance. . .
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:38
Joined
Aug 30, 2003
Messages
36,118
The error would imply that the table name isn't coming through. What does the Debug on that produce? The problem may be that you've declared the variables twice.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 04:38
Joined
Dec 28, 2004
Messages
358
Thanks for the response pbaldy

I searched and there are no variables declared twice, at least that I can find.
Also the debug print on the variables all come through as correct or expected. .

This piece of code has worked for years for me, not sure why its not working on this particular server share or database. . .

puzzzling!
 

sportsguy

Finance wiz, Access hack
Local time
Today, 04:38
Joined
Dec 28, 2004
Messages
358
The answer with some testing is that the OPTIONAL in the function is not being recognized, not sure why, but that is the answer!

thanks PBALDY, for pointing me in the correct direction!

sportsguy
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:38
Joined
Aug 30, 2003
Messages
36,118
Glad you got it working.
 

Users who are viewing this thread

Top Bottom