VB SQL trouble

**Killer42** · Sep 15 '07, 01:20 PM

I'm not sure about the error message, but I am sure that adding ".Emp_ID" to the table names was not the correct response. You are listing tables in the FROM clause, not fields.

I'm going to move this to the Access forum, where you'll find the real experts on this kind of thing.

**Scott Price** · Sep 15 '07, 01:53 PM

Originally posted by wizardRahl

Hi all,

I'm having a problem with some SQL code in VB I'm using for Access. I have a combo that allows a user to select an employee and opens a query to display its results. Yesterday, this code seemed to work fine and give the proper results. There have been no changes to this code since yesterday.

[CODE=vb]
Private Sub cboEmpName_Afte rUpdate()

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
strSql = "SELECT * FROM tblD10, tblD11, tblD12, tblD13, " & _
"tblD14, tblD15, tblD21, tblD24, tblD25, tblD26, tblD29, " & _
"tblD30, tblD31, tblD86, tblD90 " & _
"WHERE Emp_ID = " & cboEmpName & ";"

Set rs = db.OpenRecordse t(strSql, dbOpenSnapshot)

With db
Set qdf = .CreateQueryDef ("tmpEmpSearch" , strSql)
DoCmd.OpenQuery "tmpEmpSear ch"
.QueryDefs.Dele te "tmpEmpSear ch"
End With

db.Close
qdf.Close

End Sub
[/CODE]

Trying to update the combo box now gives the following error :

""The specified field <Emp_ID> could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)""

I realize that it could refer to more than one table... that's what I would like.

Clicking the help button on the error tells me to add the .<field> to the table, making the code like this: (the spacing may be off, but the syntax is correct.)

[CODE=vb]
Set db = CurrentDb()
strSql = "SELECT * FROM tblD10.Emp_ID, tblD11.Emp_ID, " & _
"tblD12.Emp _ID, tblD13.Emp_ID, tblD14.Emp_ID, tblD15.Emp_ID, " & _
"tblD21.Emp _ID, tblD24.Emp_ID, tblD25.Emp_ID, tblD26.Emp_ID, " & _
"tblD29.Emp _ID, tblD30Emp_ID, tblD31Emp_ID, tblD86Emp_ID, tblD90Emp_ID " & _
"WHERE "tblD10.Emp _ID, tblD11.Emp_ID, " & _
"tblD12.Emp _ID, tblD13.Emp_ID, tblD14.Emp_ID, tblD15.Emp_ID, " & _
"tblD21.Emp _ID, tblD24.Emp_ID, tblD25.Emp_ID, tblD26.Emp_ID, " & _
"tblD29.Emp _ID, tblD30.Emp_ID, tblD31.Emp_ID, tblD86.Emp_ID, tblD90.Emp_ID = " & cboEmpName & ";"
[/CODE]

This produces an error saying that "C:/...MyDocuments/tblD10.mdb" could not be found. The help on this doesn't give much info.

Anyone know what might cause the inital code to stop working?

Any insight appreciated,
Thanks in advance.

The first error you received could be produced by a change in the relationships between your tables, any changes at all in the db structure could be affecting this.

The second error makes me wonder if you are working on a linked db? (And I'm with Killer... the .<field> needs to be added into the SELECT part of the sql, not the FROM part...)

Probably the easiest way to troubleshoot SQL statements is to create a query in design view, and fine tune the language/criteria in design view first. Then you can copy and paste over to the vba code, making the minor changes nec to point your criteria to a form control.

Regards,
Scott

**JKing** · Sep 15 '07, 02:34 PM

Emp_Id is in multiple tables so you need to qualify it in your WHERE statement.

Example:
[code=vb]
strSQL = "SELECT * FROM table1, table2, table3 " & _
"WHERE table1.field1 = " & Me.combo1 & _
" table2.field1 = " & Me.combo1 & _
" table3.field1 = " & Me.combo1
[/code]

**JConsulting** · Sep 15 '07, 02:42 PM

Originally posted by wizardRahl

Hi all,

I'm having a problem with some SQL code in VB I'm using for Access. I have a combo that allows a user to select an employee and opens a query to display its results. Yesterday, this code seemed to work fine and give the proper results. There have been no changes to this code since yesterday.

Code:

 
Private Sub cboEmpName_AfterUpdate()

 Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim strSql As String
 Emp_ID = " & cboEmpName & ";"

    Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

    With db
        Set qdf = .CreateQueryDef("tmpEmpSearch", strSql)
        DoCmd.OpenQuery "tmpEmpSearch"
        .QueryDefs.Delete "tmpEmpSearch"
    End With
    
db.Close
qdf.Close

End Sub

Trying to update the combo box now gives the following error :

""The specified field <Emp_ID> could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)""

I realize that it could refer to more than one table... that's what I would like.

Clicking the help button on the error tells me to add the .<field> to the table, making the code like this: (the spacing may be off, but the syntax is correct.)

Code:

        Set db = CurrentDb()
      strSql = "SELECT * FROM tblD10.Emp_ID, tblD11.Emp_ID, " & _
      "tblD12.Emp_ID, tblD13.Emp_ID, tblD14.Emp_ID, tblD15.Emp_ID,  " & _
      "tblD21.Emp_ID, tblD24.Emp_ID, tblD25.Emp_ID, tblD26.Emp_ID, " & _
       "tblD29.Emp_ID, tblD30Emp_ID, tblD31Emp_ID, tblD86Emp_ID,  tblD90Emp_ID " & _
   "WHERE "tblD10.Emp_ID, tblD11.Emp_ID, " & _
            "tblD12.Emp_ID, tblD13.Emp_ID, tblD14.Emp_ID, tblD15.Emp_ID,  " & _
            "tblD21.Emp_ID, tblD24.Emp_ID, tblD25.Emp_ID, tblD26.Emp_ID, " & _
"tblD29.Emp_ID, tblD30.Emp_ID, tblD31.Emp_ID, tblD86.Emp_ID, tblD90.Emp_ID = " & cboEmpName & ";"

This produces an error saying that "C:/...MyDocuments/tblD10.mdb" could not be found. The help on this doesn't give much info.

Anyone know what might cause the inital code to stop working?

Any insight appreciated,
Thanks in advance.

Ummm, ok.

The approach you're taking makes sense in theory, but since you're bound by the syntax and logic of Access...let us help you out.

You need to create a union query out of all those tables, and save it.
Can we assume that all those tables have the same fields? If not, this gets complicated..so

to do that you simply use something like

Select * from Table1
Union
select * from table2
union
etc...

Let's call it EmployeeUnion

now, you have a single recordsource that you can reference in your SQL .

"Select * from EmployeeUnion WHERE Emp_ID = " & cboEmpName & ";"

That should get you past the hump. Let us know.
J

**wizardRahl** · Sep 19 '07, 12:15 PM

The tables really only have 1 common field; Emp_ID.
Also, I want to return all the values in tblDxx that match the combobox selection.

The database is on my hard drive and has no / is not linked in any way.
I wlll give some of the suggestions a try and post an update.

Thanks again for the help.

**wizardRahl** · Sep 19 '07, 01:42 PM

Originally posted by JConsulting

Ummm, ok.

The approach you're taking makes sense in theory, but since you're bound by the syntax and logic of Access...let us help you out.

You need to create a union query out of all those tables, and save it.
Can we assume that all those tables have the same fields? If not, this gets complicated..so

to do that you simply use something like

Select * from Table1
Union
select * from table2
union
etc...

Let's call it EmployeeUnion

now, you have a single recordsource that you can reference in your SQL .

"Select * from EmployeeUnion WHERE Emp_ID = " & cboEmpName & ";"

That should get you past the hump. Let us know.
J

So you're making query called "EmployeeUnion" ? Then, querying a query?
This would be really helpful.

Thanks again.

VB SQL trouble

VB SQL trouble

Comment

Comment

Comment

Comment

Comment

Comment