Link to home
Start Free TrialLog in
Avatar of jjjjjjj
jjjjjjj

asked on

SQL won't work

I have a data control on a form which is used to link to an Access Db.

In the formload event of the form I have the following code:

Set dbs = OpenDatabase("c:\my documents\employees.mdb")

In the general declarations section:

Option Explicit
Dim dbs As Database
Dim rst As Recordset

And in a command button:

Private Sub Command1_Click()




Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, " _
& "tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle, " _
& "FROM tbldept INNER JOIN tblemployees ON " _
& "tbldept.DeptNumber = tblemployees.EmpDeptNumber, " _
& "WHERE DeptNumber ='" & Text1.Text & "' ;")

I cannot get the SQL coded properly.  The error is 3141.

What is wrong with the SQL?

TIA,

jjjjjjj
Avatar of AzraSound
AzraSound
Flag of United States of America image

try putting parenthesis around the FROM clause:

FROM (tbldept INNER JOIN ... ON ....)
ASKER CERTIFIED SOLUTION
Avatar of AzraSound
AzraSound
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry, before the FROM as well
Avatar of Éric Moreau
Definitively, the problem is caused by commas!
Avatar of mark2150
mark2150

Why is there a ; at the end of things?


M
to tell that the statement is over
I would create the Query in Access and click on view\sql copy that and paste it in your code.

Also I would:
dim sSql as string

sSql = "Select * from Table1"
sSql = Sql & "Where Table1.ID = 1 "

Debug.Print Sql 'If you copy this 'string into the sql window in access 'and it runs you are good to go.

'Set rst = dbs.OpenRecordset sSqL

Check your Syntax before you even try and open the rs.

The statement is over when the string ends. Unless this is embedded in a stream of statements you don't need the ";" at the end. This may be causing the SQL engine to think that more SQL is forthcomming when it's not.

M
To make it a little easier, create your SQL in Access design mode, view it in SQL mode and paste it in your code.
Avatar of jjjjjjj

ASKER

All,

I have done the Access look and paste but it has not helped.  I believe that there is a syntax error in the code that I have written, but I cannot find it.

I will take the suggestions that AzraSound has made and see what happens.

Thanks for the great dialog from all!!

jjjjjjj
yeah, I agree with Mark.
You won't need ';'.
NO Commas before FROM and before WHERE.

last statement should be:

"where DeptNumber = '" & Text1.Text& "'"

ALSO, are you sure it's _

to continue?
Is it    " & _
 "where


Good Luck
Bill
Why isn't working?

Is your query is this one:

Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, " _
& "tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle " _
& "FROM tbldept INNER JOIN tblemployees ON " _
& "tbldept.DeptNumber = tblemployees.EmpDeptNumber " _
& "WHERE DeptNumber ='" & replace(Text1.Text,"'","''") & "'")
Avatar of jjjjjjj

ASKER

Yes, it is, but in VB All of the data after the replace is not needed.  I have used the code that I posted in the past but never with the join spec.  I believe that this is where the problem lies, but can't figure it out.

jjjjjjj
Is DeptNum numeric?

If so your query should be:
Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle FROM tbldept INNER JOIN tblemployees ON tbldept.DeptNumber = tblemployees.EmpDeptNumber WHERE DeptNumber =" & Text1.Text)

While your query won't work, put it all on a single line so it could be easier to seek for mistake!
go database manager, put all of your SQL statment and test it over there. You should get the result immedailly, and other thing is try not to break the SQL statement into different line. Cuase you may make some mistake when u try to break it into different line. Other method that u can try is go to access and create a queue for your SQL statement, then compare the SQL code inside the access and your old SQL statement. Ususally u are able to get some idea from the SQl code.


Using Access you will need ; at the end of your select statment.  Access SQl Also needs:

String = "text1"
AutoNumber = Text1
Avatar of jjjjjjj

ASKER

Thanks, AzraSound.  The comma's were the culprit.  The parentheses can either stay or remain, either way the SQL now works fine.

jjjjjjj