Topic: SQL statements in VBA in Access

[Kheilmann]

I may be going about it wrong, but...
I think I need help creating a SQL string which will open a record from a table and look for certain fields and apply them to variables I created.
I keep getting a Data conversion Error...
My code:
Dim Job as string
Dim Builder as string
Dim Plan as string
Job = Me.JobEntry.value ' Custom dialog JobSearch with TextBox 'JobEntry'
SQLstring = "Select * From [Set Up] Where [Set Up].[Job Number] ='" & JOb & "';"
'This works in my Cad VBA, I guess Access is different?
Set Jobrs = currentdb.openrecordset(sqlstring, Dynaset)
'I get conversion error here....I assume my Sql string is wrong...

I do have my form run a query and bring back the record of whatever JOb# is in the JObEntry textbox.
Access VBA uses the statement...
Docmd.openquery stDocName, AcNormal, AcReadOnly
Can I access the other fields from this and apply them to my variables??

I'm new to access so I may very well be way off course....
oh, I hope this is the correct area for this Question...

[MickD]

Apart from the typo (Job and JOb ?) maybe your data types in the db are not all 'text' types??

Also, just an observation, having spaces in you column names can be hazardous when creating sql queries in different api's.

Just something to try 'till some who really knows what there doing comes along

[Kheilmann]

Thanks,
I checked the Job Number datatype in my table and it is a Text Field. 
I also tried using the ' mark around the string variable, with no luck...
ie.  Sql = "Select * From [Set Up] Where [Set Up].[Job Number] = ' " & Job & " ';"
I used spaces to show the ' mark and " mark.  I've also tried without....

The DB  was created 10 or 11 years ago, and I'd hate to go in and start messing with things like that....
I'm trying to get them to upgrade their many separate utilities into a centralized system...but until then...

I'll keep trying to figure this out until someone shows me the light.

[quicksilver]

One trick that I find helpful is to open a new query and go to the SQL view.  Copy and paste your sql statement in replacing the Job variable with an actual entry.  This may help you if there are any errors in the calling of the fields etc.  Make whatever changes are required to get it work and then cut and paste the SQL code back into your VBA replacing the text with the variable.

[Rob_Kish]

There can be no spaces between the single quote and the Job Number. When this runs, result is:

                "Select * From [Set Up] Where [Set Up].[Job Number] = ' Job';"

And there probably isn't a job in dBase matching that (with space in Job Number).

[Dnereb]

First Question.... is this code in Acad opening an Access databse or in Access?

Assumming you are in Access VBA:

Dim Job as string
Dim Builder as string
Dim Plan as string
Dim JobRs as DOA.Recordset

Job = Me.JobEntry.value ' Custom dialog JobSearch with TextBox 'JobEntry'
SQLstring = "Select * From [Set Up] Where [Set Up].[Job Number] ='" & JOb & "';"
Debug.Print SQLString 'To this always if you build an SQL String and look in the inmediate window for the result
'This works in my Cad VBA, I guess Access is different?
Set Jobrs = currentdb.openrecordset(sqlstring, dbOpenDynaset)


You can access your database through the Docmd but I warn you it's an old legacy and with some commands it doesn't run in Sync.
You can use DAO, ADO and SQL as well.
To maximize performance use SQL in your recordset to filter out the specific record you want with a WHERE clausule. The bottleneck is transfering data from your database to your application.

[Kheilmann]

My note about the spaces was in reference to the sentence on this forum...
I added a space so it would be easier to read...There is no space in my code...

Thanks Dnereb....

My whole problem was the fact that I had dynaset in my Set recordset statement instead
of dbOpenDynaset.

Thanks for everyones help

[Dnereb]

Note:
Be sure to declare your Recordset as DAO as well if you are using DAO of course for
compability reasons. Some versions of Access use ADO as Default.
Flunking on assinging a recordset through the currentdb object (DAO)