How to open a recordset with an SQL statement based on two tables?

[Adam Krusk]

Hello everyone.

I'm trying to construct a database in Microsoft Access 2003. The first person that is going to use the database wishes to do all her work in Excel and then the database is supposed to be updated by importing the Excel document into my Access database. This means that I will have a big table that is imported from Excel. Since I'm pretty sure that later users will want to be able to update things directly in the database, I'm trying to split up the big table into smaller tables to make the database a little more manageable. As a part of this I want to check for a text in the big table, find the same text and a corresponding ID number in a sub table and in a third table save the ID number. To do this I have written the following code where the big table is "Hazard List Bridge", the sub table is "subTbl_LinkSpe cific" and the table where the result should be stored is "tbl_Hazard ".

Dim rstHazardsFromExcel As DAO.Recordset
Dim rstHazard As DAO.Recordset
Dim rstLinkSpecific As DAO.Recordset
Dim strSQLLinkSpecific As String

'Here is code that is working fine and that is not relevant to the question

Set rstHazardsFromExcel = curDatabase.OpenRecordset("Hazard List Bridge")
Set rstHazard = curDatabase.OpenRecordset("tbl_Hazard")

With rstHazardsFromExcel
    For Each fld1 In .Fields
        Do Until .EOF
            rstHazard.AddNew
            strSQLLinkSpecific = "SELECT KEY_LinkSpecific FROM subTbl_LinkSpecific, [Hazard List Bridge] WHERE ID_LinkSpecific = SpecifikOrGeneral"
            Set rstLinkSpecific = curDatabase.OpenRecordset(strSQLLinkSpecific, dbOpenDynaset)
            rstHazard![ID_LinkSpecific] = rstLinkSpecific![KEY_LinkSpecific]
            rstHazard.Update
            .MoveNext
        Loop
    Next
End With

'Here is code that is working fine and that is not relevant to the question

As mentioned in the code I have omitted parts of the code but that code is working fine and the problem comes when I'm trying to open the recordset with the SQL string strSQLLinkSpeci fic (line 16). I get the following error:

"Run-time error 3061 - Too few parameters. Expected 1"

Clearly something is wrong with the way I'm trying to open a recordset but do you have any idea what I should change to be able to do what I want, i.e. check for a text in the big table, "Hazard List Bridge", find the same text and a corresponding ID number in a sub table, "subTbl_LinkSpe cific", and in a third table, "tbl_Hazard ", save the ID number?

Sincerely

Adam Krusk

PS

ID_LinkSpecific and SpecifikOrGener al are columns in subTbl_LinkSpec ific and "Hazard List Bridge" if that was unclear.

DS

[Mariostg]

Does the query works if you run it with the query builder?

[Adam Krusk]

Hi Mariostg and thanks for your quick reply!

I must confess that I'm new to working with Access and databases in general so asking about the query builder was a good thing! After having learnt how it works I realised that I had written the wrong columns in the query. This was solved by changing

strSQLLinkSpeci fic = "SELECT KEY_LinkSpecifi c FROM subTbl_LinkSpec ific, [Hazard List Bridge] WHERE ID_LinkSpecific = SpecifikOrGener al"

to

strSQLLinkSpeci fic = "SELECT subTbl_LinkSpec ific.KEY_LinkSp ecific FROM subTbl_LinkSpec ific, [Hazard List Bridge] WHERE [Hazard List Bridge].[General railway hazard or specific to Link] = subTbl_LinkSpec ific.SpecifikOr General"

From now on I will try to always write the table names to make the code more readable when querying from more than one table. Anyway, this solved the problem with the run-time error 3061 but the code still didn't do what I wanted it to.

rstHazard![ID_LinkSpecific] = rstLinkSpecific ![KEY_LinkSpecifi c]

was always given the same value no mater the value in the table SpecifikOrGener al. What I didn't realise was that

[Hazard List Bridge].[General railway hazard or specific to Link]

of course will always build up the same table from which I always take the value in the first row. Anyway to make a long story short I have now changed the code to

Dim rstHazard As DAO.Recordset
Dim rstLinkSpecific As DAO.Recordset
Dim strSQLLinkSpecific As String

'Code that is working fine and that is not relevant to the question

Set rstHazardsFromExcel = curDatabase.OpenRecordset("Hazard List Bridge")
Set rstHazard = curDatabase.OpenRecordset("tbl_Hazard")
intCounter = 0
With rstHazardsFromExcel
    For Each fld1 In .Fields
        Do Until .EOF
            rstHazard.AddNew
                rstHazard![KEY_Hazard] = intCounter
                rstHazard![ID_Centralhazard] = ![Hazard identification index]
                If Not IsNull(rstHazardsFromExcel![General railway hazard or specific to Link]) Then
                    strTEST = Chr(34) & rstHazardsFromExcel![General railway hazard or specific to Link] & Chr(34)
                    strSQLLinkSpecific = "SELECT subTbl_LinkSpecific.KEY_LinkSpecific FROM subTbl_LinkSpecific, [Hazard List Bridge] WHERE subTbl_LinkSpecific.SpecifikOrGeneral = " & strTEST
                    Set rstLinkSpecificNEW = curDatabase.OpenRecordset(strSQLLinkSpecific)
                    rstHazard![ID_LinkSpecific] = rstLinkSpecificNEW![KEY_LinkSpecific]
                    rstHazard.Update
                    intCounter = intCounter + 1
                End If
                .MoveNext
        Loop
    Next
End With

and now the code does what I want it to. Thanks again Mariostg. Your short answer was the thing that headed me in the right direction.

[NeoPa]

I would say two things :

1. It's almost never a good idea to split similar data into multiple tables. An RDBMS (as Access is) works much better with the data stored together in a table than it does with multiple tables. The overheads are less in the first scenario.
2. JOINs are generally required to do what you want. There is a great deal available (on the web and even in the Help system under Microsoft Jet SQL) on SQL for Access, but SQL JOINs may give you some help to understand some of the issues.