Insert statement using ADO with Access DB and SQL DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Set dbConnect = New ADODB.Connection
strProviderA = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\supply
discount - data.mdb"


strSQL = "SELECT ARIBC.BTCHDESC, ARIBC.BTCHTYPE, ARIBC.INVCTYPE,
ARIBH.CNTBTCH, ARIBH.CNTITEM, " & _
"ARIBH.IDCUST, ARIBH.IDINVC, ARIBH.INVCDESC, ARIBH.FISCYR,
ARIBH.DATEINVC, " & _
"ARIBH.AMTINVCTOT into AR_Batch " & _
"FROM " & _
"[Provider=MSDASQL;DSN=VGCDAT-live.ARIBC],
[Provider=MSDASQL;DSN=VGCDAT-live.ARIBH] " & _
"WHERE ARIBC.CNTBTCH = ARIBH.CNTBTCH " & _
"AND ARIBC.BTCHDESC= '" & strBatchDescs & "' AND ARIBH.FISCYR=
'" & strYear & "'"

dbConnect.Open strProviderA
dbConnect.Execute strSQL

dbConnect.Close
Set dbConnect = Nothing
Exit Sub
 
I get the following error message:

Microsoft JET Database Engine

-2147217865 The Microsoft Jet database engine cannot find the input table
or query 'Provider=MSDASQL;DSN=VGCDAT-live.ARIBC'. Make sure it exists and
that its name is spelled correctly


VGCDAT-live is an ODBC to my PERVASIVE SQL database.
ARIBC and ARIBH are just 2 of the tables.
I want to insert certain records from these table into my access database to
work with.

What I have done wrong?
 
I'm not sure but can this help?

[Provider=MSDASQL;DSN=VGCDAT-live.ARIBC]

=> [Provider=MSDASQL;DSN=VGCDAT-live].[ARIBC]

I have never seen this syntax before

- Raoul

Ron said:
I get the following error message:

Microsoft JET Database Engine

-2147217865 The Microsoft Jet database engine cannot find the input table
or query 'Provider=MSDASQL;DSN=VGCDAT-live.ARIBC'. Make sure it exists and
that its name is spelled correctly


VGCDAT-live is an ODBC to my PERVASIVE SQL database.
ARIBC and ARIBH are just 2 of the tables.
I want to insert certain records from these table into my access database to
work with.

What I have done wrong?

Ron said:
Set dbConnect = New ADODB.Connection
strProviderA = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\supply
discount - data.mdb"


strSQL = "SELECT ARIBC.BTCHDESC, ARIBC.BTCHTYPE, ARIBC.INVCTYPE,
ARIBH.CNTBTCH, ARIBH.CNTITEM, " & _
"ARIBH.IDCUST, ARIBH.IDINVC, ARIBH.INVCDESC, ARIBH.FISCYR,
ARIBH.DATEINVC, " & _
"ARIBH.AMTINVCTOT into AR_Batch " & _
"FROM " & _
"[Provider=MSDASQL;DSN=VGCDAT-live.ARIBC],
[Provider=MSDASQL;DSN=VGCDAT-live.ARIBH] " & _
"WHERE ARIBC.CNTBTCH = ARIBH.CNTBTCH " & _
"AND ARIBC.BTCHDESC= '" & strBatchDescs & "' AND ARIBH.FISCYR=
'" & strYear & "'"

dbConnect.Open strProviderA
dbConnect.Execute strSQL

dbConnect.Close
Set dbConnect = Nothing
Exit Sub
Click to expand...
Click to expand...
 
Back
Top