DAO Truncate (1 Viewer)

T

Tupacmoche

Registered User.

Local time

Today, 09:17

Joined

Apr 28, 2008

Messages

291

• Aug 7, 2018

• #1

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

bit cruncher

Local time

Today, 06:17

Joined

Mar 17, 2004

Messages

8,179

• Aug 7, 2018

• #2

Tupacmoche said:

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

Click to expand...

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

 

T

Tupacmoche

Registered User.

Local time

Today, 09:17

Joined

Apr 28, 2008

Messages

291

• Aug 7, 2018

• #3

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

bit cruncher

Local time

Today, 06:17

Joined

Mar 17, 2004

Messages

8,179

• Aug 7, 2018

• #4

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...

Code:Copy to clipboard

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

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 23:17

Joined

Jan 20, 2009

Messages

12,851

• Aug 7, 2018

• #5

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.

 

T

Tupacmoche

Registered User.

Local time

Today, 09:17

Joined

Apr 28, 2008

Messages

291

• Aug 7, 2018

• #6

Hi Galaxiom,

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