append query using ADO

[Acie]

I have an append query that runs perfectly in access.
However, when i try to run it from vba using ado it runs,
provides no errors
but it doesn't append data to the existing table. (and
there are thousands of records...)

environment Access '02 & Windows2k

code below.. (snippet)

Dim cn As New ADODB.Connection
Const dbpath = "C:\qa_sys.mdb"

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
dbpath

sql_str2 = "SELECT dbo_CUV_TOP_HOLDER.HOLDER_TYPE,
dbo_CUV_TOP_HOLDER.top& UserId & "_FBO_Step1A.NM_LGL,
dbo_CUV_TOP_HOLDER.bot INTO " & UserId & "_FBO_Step2A "
(spared you the rest of a long query)

cn.Execute sql_str2,, adcmdtext

that is it. the query runs, but it does not append any
records. If i create a stored procedure with this query.
and i run it from Access it appends 2k records.

thanks, for any help!
Acie

 

[Guest]

maybe I'm oversimplifying, but are you sure you want "SELECT dbo_CUV_TOP_HOLDER...", or do you want "INSERT dbo_CUV_TOP_HOLDER..."

 

[Guest]

Actually, it is a make-table query, not an true append
query.

I've tried your suggestion though, and made it a true
append query, I also increased the time parameter. Still
it doesn't work. (I'm about to give up on ADO and revert
back to reliable DAO!). IN DAO, it works perfectly fine.

thanks,


Any other suggestions???

-----Original Message-----
maybe I'm oversimplifying, but are you sure you

want "SELECT dbo_CUV_TOP_HOLDER...", or do you
want "INSERT dbo_CUV_TOP_HOLDER..."

 

[Acie]

Actually, it is a make-table query, not an true append
query.

I've tried your suggestion though, and made it a true
append query, I also increased the time parameter. Still
it doesn't work. (I'm about to give up on ADO and revert
back to reliable DAO!). IN DAO, it works perfectly fine.

thanks,


Any other suggestions???

-----Original Message-----
maybe I'm oversimplifying, but are you sure you

want "SELECT dbo_CUV_TOP_HOLDER...", or do you
want "INSERT dbo_CUV_TOP_HOLDER..."