Connecting to a database via ODBC connection

[cjohnny]

Hi, Everyone
I am very new to VB scripting. I am trying to query for specific information in a database via ODBC connection that has many fields. I only want to display certain fields that match my criteria. The table name has a space.I am not sure I need to include the server name, user name and password. Here is what I have so far just to test connection but I am getting "expected end of statement". Thanks everyone in advance.
Once I have this piece done, I next thing I want to do is have a routine that does a text-to-speech of the result.

P.S. I have no control over the naming convention.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection&quot
Set objRecordset = CreateObject("ADODB.Recordset&quot
objConnection.Open "DSN=ticketquery;server=myserver;uid=cjohnny;pwd=password;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT "My Case"."Assigned To","My Case"."User Description" FROM "My Case" WHERE "My Case"."Assigned To"='cjohnny' , objConnection adOpenStatic, adLockOptimistic"
objRecordset.Close
objConnection.Close

 

[Bullschmidt]

objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT "My Case"."Assigned To","My Case"."User Description" FROM "My Case" WHERE "My Case"."Assigned To"='cjohnny' , objConnection adOpenStatic, adLockOptimistic"
objRecordset.Close
objConnection.Close

I'm not too sure about adUseClient as I've never used that.

But in the SQL statement itself I would suggest changing this:
"SELECT "My Case"."Assigned To","My Case"."User Description" FROM "My Case" WHERE "My Case"."Assigned To"='cjohnny'

To be more like this instead (and note the double quote at the end to end the string):
"SELECT [My Case].[Assigned To],[My Case].[User Description] FROM [My Case] WHERE [My Case].[Assigned To]='cjohnny'"

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer

http://www.Bullschmidt.com

ASP design tips, demo database on the Web, free barchart tool...

 

[cjohnny]

Thanks Bullschmidt for the reply. But I am getting the following error msg:
Error: The connection cannot be used to perform this operation. It is either closed or invalid in this context.
Code: 800A0E7D
Source: ADODB.Recordset

What I am doing wrong???Thanks