help with VB-driven Microsft Jet SQL 
Author Message
 help with VB-driven Microsft Jet SQL

Hello!

I'm attempting to use an ADO connection to throw sql
statements at the Microsoft Jet engine to do two things.  
First, I need to import data from an Excel 5.0 spreadsheet
with a worksheet named ProductCode into an Access 97
database with a table named tblProductCode, selecting only
specific records.  Then I need to copy the data from the
first database to another Access 97 database with a table
of the same name, again restricting that copy to only
specific records.

The first sql statement comes out as:

SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U FROM
[ProductCode$] IN "C:\Program Files\Piebald
Software\DBC\Spreadsheets\ProductCode.xls" "EXCEL 5.0;"
WHERE A LIKE "Z*";

The second sql statement comes out as:

SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U INTO
tblProductCode FROM tblProductCode IN "C:\Program
Files\Piebald Software\Data\CBSi.mdb" WHERE A = "Z01";

Both cases result in the following error:

"No value given for one or more required parameters"

Does anyone have any ideas?  Recommend any change in the
approach?  Use any method other than the ADO? Any help
will be greatly appreciated!

Code
----

Public cnnDB As New ADODB.Connection
Public cmdSQL As New ADODB.Command

'
' case #1
'
cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnDB.ConnectionString = OutputDatabasePath + "\" +
MasterDatabaseName
cnnDB.Open
'
cmdSQL.ActiveConnection = cnnDB
'
cmdSQL.CommandText = "SELECT
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U "
cmdSQL.CommandText = cmdSQL.CommandText + "FROM
[ProductCode$] "
cmdSQL.CommandText = cmdSQL.CommandText + "IN " + q +
txtProductCode.Text + q + " "
cmdSQL.CommandText = cmdSQL.CommandText + q + "EXCEL 5.0;"
+ q + " "
cmdSQL.CommandText = cmdSQL.CommandText + "WHERE A LIKE "
+ q + "Z*" + q + ";"
cmdSQL.Execute
'
cnnDB.Close

'
' case #2
'
cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnDB.ConnectionString = OutputDatabasePath + "\" +
HopeDatabaseName
cnnDB.Open
'
cmdSQL.ActiveConnection = cnnDB
'
cmdSQL.CommandText = "SELECT
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U "
cmdSQL.CommandText = cmdSQL.CommandText + "INTO
tblProductCode "
cmdSQL.CommandText = cmdSQL.CommandText + "FROM
tblProductCode "
cmdSQL.CommandText = cmdSQL.CommandText + "IN " + q +
OutputDatabasePath + "\" + MasterDatabaseName + q + " "
cmdSQL.CommandText = cmdSQL.CommandText + "WHERE A = " + q
+ "Z01" + q + ";"
cmdSQL.Execute
'
cnnDB.Close



Tue, 12 Oct 2004 23:04:08 GMT  
 help with VB-driven Microsft Jet SQL
Not sure about your second problem, but in ADO, the wildcard is %, not *.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele



Quote:
> Hello!

> I'm attempting to use an ADO connection to throw sql
> statements at the Microsoft Jet engine to do two things.
> First, I need to import data from an Excel 5.0 spreadsheet
> with a worksheet named ProductCode into an Access 97
> database with a table named tblProductCode, selecting only
> specific records.  Then I need to copy the data from the
> first database to another Access 97 database with a table
> of the same name, again restricting that copy to only
> specific records.

> The first sql statement comes out as:

> SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U FROM
> [ProductCode$] IN "C:\Program Files\Piebald
> Software\DBC\Spreadsheets\ProductCode.xls" "EXCEL 5.0;"
> WHERE A LIKE "Z*";

> The second sql statement comes out as:

> SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U INTO
> tblProductCode FROM tblProductCode IN "C:\Program
> Files\Piebald Software\Data\CBSi.mdb" WHERE A = "Z01";

> Both cases result in the following error:

> "No value given for one or more required parameters"

> Does anyone have any ideas?  Recommend any change in the
> approach?  Use any method other than the ADO? Any help
> will be greatly appreciated!

> Code
> ----

> Public cnnDB As New ADODB.Connection
> Public cmdSQL As New ADODB.Command

> '
> ' case #1
> '
> cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
> cnnDB.ConnectionString = OutputDatabasePath + "\" +
> MasterDatabaseName
> cnnDB.Open
> '
> cmdSQL.ActiveConnection = cnnDB
> '
> cmdSQL.CommandText = "SELECT
> A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U "
> cmdSQL.CommandText = cmdSQL.CommandText + "FROM
> [ProductCode$] "
> cmdSQL.CommandText = cmdSQL.CommandText + "IN " + q +
> txtProductCode.Text + q + " "
> cmdSQL.CommandText = cmdSQL.CommandText + q + "EXCEL 5.0;"
> + q + " "
> cmdSQL.CommandText = cmdSQL.CommandText + "WHERE A LIKE "
> + q + "Z*" + q + ";"
> cmdSQL.Execute
> '
> cnnDB.Close

> '
> ' case #2
> '
> cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
> cnnDB.ConnectionString = OutputDatabasePath + "\" +
> HopeDatabaseName
> cnnDB.Open
> '
> cmdSQL.ActiveConnection = cnnDB
> '
> cmdSQL.CommandText = "SELECT
> A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U "
> cmdSQL.CommandText = cmdSQL.CommandText + "INTO
> tblProductCode "
> cmdSQL.CommandText = cmdSQL.CommandText + "FROM
> tblProductCode "
> cmdSQL.CommandText = cmdSQL.CommandText + "IN " + q +
> OutputDatabasePath + "\" + MasterDatabaseName + q + " "
> cmdSQL.CommandText = cmdSQL.CommandText + "WHERE A = " + q
> + "Z01" + q + ";"
> cmdSQL.Execute
> '
> cnnDB.Close



Wed, 13 Oct 2004 07:44:40 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. help with VB-driven Microsft Jet SQL

2. Help with Microsft Internet Transfer Control

3. Microsft Word And Vb4 Help!

4. Microsft internet control and HTML source HELP!

5. Opening Microsft Exchange Server's EDB file thru VB

6. Help with BOOLEAN DATA TYPE in Jet-SQL international version

7. ?SQL/Jet VB Functions in GROUP BY

8. SQL - Jet in VB 5.0

9. Help converting JET MDB into SQL Server

10. Please help, I am new to Jet Engine and SQL

11. SQL vs VB Data Access Objects for Jet 2.0

12. Help: MS-SQL Server 6 / ODBC / Jet - A triad gone horribly wrong

 

 
Powered by phpBB® Forum Software