truncate table using dao

[Andre]

Access 97, SQL2k

How can I truncate a linked sql table using Dao? I've tried several
different ways, including the following, and continually get errors.

Dim db As DAO.Database
Dim Connect As String

Connect = "ODBC;DSN=HCPHPA;UID=xxx;PWD=xxx"

Dim strSQL
strSQL = "Truncate table dbo.Enrollment;"
db.Execute strSQL, dbSQLPassThrough

 

[Alex Dybenko]

You have to create a pass-through query first, then run it

Dim qryd As QueryDef
Set qryd = CurrentDb.CreateQueryDef("")
qryd.Connect ="ODBC;DSN=HCPHPA;UID=xxx;PWD=xxx"
qryd.sql = "Truncate table dbo.Enrollment"
qryd.ReturnsRecords = false
qryd.execute

 

[Andre]

Alex,

Thanks! You make it look so simple.

Would you be able to help me overcome another issue? I'm trying to load
data from my Access table to a linked SQL 2k table - in code. The statement
below works as long as I don't have the "Group" column included. It's
bombing because it's a keyword of course. I just don't know how to get past
it.

CurrentDb.Execute ("Insert Into dbo_Enrollment (LineOfBusiness, Provider,
[Group], [SortOrder#], Region, SiteIdx, Site, MM, Month, Year) Select
LineOfBusiness, Provider, [Group], [SortOrder#], Region, SiteIdx, Site, MM,
Month, Year from enrollment")

Thanks, Andre

 

[Alex Dybenko]

Hi Andre,
just tried the same - works fine at me (access 2003/SQL server 2000). Maybe
you have some constrain on Group field? of trigger? which do no allow you to
insert.