DAO Truncate

[Tupacmoche]

Hi VBA/SQL Masters,


I have the following vba script to truncate an SQL table. I keep getting a run-time error 3265 - Item not found in collection. But, I can't figure out what is wrong. Does anyone know how to fix it?


Private Sub btnBM_Click()
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("dbo.FinderFile").Connect
qdef.SQL = "Truncate table med.dbo.FinderFile"
qdef.ReturnsRecords = False
qdef.Execute

MsgBox ("Your file is now processing...")

End Sub

 

[MarkK]

qdef.Connect = CurrentDb.TableDefs("dbo.FinderFile").Connect

Most likely the item dbo.FinderFile does not exist in the TableDefs collection.
Mark

 

[Tupacmoche]

I did think of that and linked it into the Access app. The tables are mssql backend Is there something else, I should do?

 

[MarkK]

I'm not sure what you are trying to do, or what tables there are in your TableDefs collection. If you've linked MSSQL tables into there, they should be present in the collection. You can see what is there with code like...

Sub ShowNonSysTables()
    Dim tmp As DAO.TableDef
    
[COLOR="Green"]    'enumerate the tabledefs collection and show non-system tables[/COLOR]
    For Each tmp In CurrentDb.TableDefs
        If Left(tmp.Name, 4) <> "MSys" Then
            Debug.Print tmp.Name
        End If
    Next
End Sub

hth
Mark

 

[Galaxiom]

You need to use the local name for dbo.FinderFile.

By default it will be dbo_FinderFile because the underscore is substituted for dot which is not allowed in Access name.

 

[Tupacmoche]

Hi Galaxiom,

Thank you it was late when I was coding this and I used the dot (.) operator instead of the underscore (_). Much appreciated.