SQl query in VBA - SELECT inside an INSERT INTO

G

Guest

I am trying to migrate data from a formatted text file into an Access 2003
Database.
I have a DB with 3 tables called Airport, Runway and Obstacle.
Here is the code:
Private Sub cmdCreate_Click()

Dim mySQL, mySQL2, InputData As String
Dim strIATA, strApt, strCity, strCntry As String
Dim intElev, intNbrOfRwys As Integer
Dim strRwy As String
Dim intSrfc, intTORA, intASDA, intTODA, intLDA, intLUDst, intNbrOfObs, _
intMonth, intDay, intYear As Integer
Dim dblSlope As Double
Dim strDate As String
Dim intObsHt, intObsDst, ObsTurnDst As Integer

Open "C:\Airport\Runway.dat" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
Line Input #1, InputData ' Read line of data.
Debug.Print InputData ' for debugging - Print to the Immediate
window.
strIATA = Mid(InputData, 3, 4) ' this is the primary key - IATA
code is unique
strApt = Mid(InputData, 15, 18)
strCity = Mid(InputData, 35, 18)
strCntry = Mid(InputData, 61, 12)
intElev = Val(Mid(InputData, 54, 4))
intNbrOfRwys = Val(Mid(InputData, 8, 2))

'TODO Write the airport data to a record in the airport table

mySQL = "INSERT INTO Airport (IATACode, AirportName, CityName,
Country, Elevation, NumberOfRunways )"
mySQL = mySQL + " VALUES ('" & strIATA & "', """ & strApt & """, """
& strCity & """, '" & strCntry & "',"
mySQL = mySQL + "" & intElev & ", " & intNbrOfRwys & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

For x = 1 To intNbrOfRwys
Line Input #1, InputData
Debug.Print InputData
strRwy = Mid(InputData, 7, 7)
intSrfc = Val(Mid(InputData, 14, 1))
intTORA = Val(Mid(InputData, 15, 5))
intASDA = Val(Mid(InputData, 21, 5))
intTODA = Val(Mid(InputData, 27, 5))
intLDA = Val(Mid(InputData, 33, 5))
dblSlope = Val(Mid(InputData, 40, 5))
intLUDst = Val(Mid(InputData, 46, 4))
intNbrOfObs = Val(Mid(InputData, 53, 2))
intMonth = Val(Mid(InputData, 61, 2))
intDay = Val(Mid(InputData, 63, 2))
intYear = Val(Mid(InputData, 65, 4))

strDate = Str(intDay) & "/" & Str(intMonth) & "/" & Str(intYear)

'TODO Write runway data to a record in the runway table

mySQL = "INSERT INTO Runway ([Date], RunwayDesignator,
SurfaceIndicator, TORA, ASDA, TODA, LDA,"
mySQL = mySQL + " Slope, LineUpDistance, NumberOfObstacles,
IATACode)"
mySQL = mySQL + " VALUES ('" & strDate & "', """ & strRwy & """,
" & intSrfc & ", " & intTORA & ","
mySQL = mySQL + "" & intASDA & ", " & intTODA & ", " & intLDA &
", " & dblSlope & ","
mySQL = mySQL + "" & intLUDst & ", " & intNbrOfObs & ", '" &
strIATA & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

All works fine to here. The Airport and Runway tables populate perfectly.
The runway table has a field called RunwayID (Primary Key - autonumber type)
that I want to put into the Obstacle table as well to be a foreign key, so I
thought I would build mySQL2 and put it in the mySQL string below. Since the
record has just been written to the Runway table above, a SELECT should give
me RunwayID where IATACode and RunwayDesignator are equal to the strings
values just written to the runway table. I've tried numerous variations and
just cannot get the syntax right.
Any and all help much appreciated.

mySQL2 = "SELECT RunwayID FROM Runway WHERE IATACode = '" &
strIATA & "'"
mySQL2 = mySQL2 + " And RunwayDesignator = """ & strRwy & """"

For y = 1 To intNbrOfObs
Line Input #1, InputData
Debug.Print InputData
intObsHt = Val(Mid(InputData, 42, 4))
intObsDst = Val(Mid(InputData, 47, 5))
intObsTurnDst = Val(Mid(InputData, 53, 5))

'TODO Write obstacle data to a record in the obstacle table

mySQL = "INSERT INTO Obstacle (Height, Distance,
TurnDistance, RunwayDesignator, IATACode, RunwayID)"
mySQL = mySQL + " VALUES(" & intObsHt & ", " & intObsDst &
", " & intObsTurnDst & ","
mySQL = mySQL + """" & strRwy & """, '" & strIATA & "', " &
mySQL2 & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
Next y
Next x

Loop
Close #1 ' Close file.

End Sub
 
S

Smartin

Simmer2 said:
I am trying to migrate data from a formatted text file into an Access 2003
Database.
I have a DB with 3 tables called Airport, Runway and Obstacle.
Here is the code:
Private Sub cmdCreate_Click()

Dim mySQL, mySQL2, InputData As String
Click to expand...

Without picking though the code too much, let's pause here for a moment.

Declarations such as these in VB[A] do not do what I think you assume
they are doing. E.g.,

Dim mySQL, mySQL2, InputData As String

creates InputData as a string, but mySQL, mySQL2 are variants.

To make all of these variables strings you must declare them explicitly:

Dim mySQL As String
Dim mySQL2 As String
Dim InputData As String

Suggest you clean up that piece and go from there.

The other debugging tools you probably want to try are setting
breakpoints and calling Debug.Print before key executions to check the
state of your variables.

Hope this helps!
 
S

Smartin

Simmer2 said:
Thanks for the help.
I have changed all the declarations as you suggested.
All still works to the point I outlined.
Problem is in the format of mySQL2 - I'm pretty sure. If I remove mySQL2
from last mySQL string for Obstacle table all works just fine. That is
Obstacle table populates fine but is of course missing data for the RunwayID
field.
Click to expand...

Hi Simmer2,

Right after you are done concatenating mySQL2, try inserting these
statements:

Debug.Print mySQL2
Stop

Does the output look correct? Post it here if you are still having trouble.
 
V

Van T. Dinh

Just before the last set of statement to execute the SQL, add the Debug
statement like:
Debug.Print mySQL
' Your existing code
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

When you run the code, the String mySQL will be printed in the Immediate /
Debug window. Please copy that String and post it back here.

--
HTH
Van T. Dinh
MVP (Access)



Simmer2 said:
I am trying to migrate data from a formatted text file into an Access 2003
Database.
I have a DB with 3 tables called Airport, Runway and Obstacle.
Here is the code:
Private Sub cmdCreate_Click()

Dim mySQL, mySQL2, InputData As String
Dim strIATA, strApt, strCity, strCntry As String
Dim intElev, intNbrOfRwys As Integer
Dim strRwy As String
Dim intSrfc, intTORA, intASDA, intTODA, intLDA, intLUDst, intNbrOfObs,
_
intMonth, intDay, intYear As Integer
Dim dblSlope As Double
Dim strDate As String
Dim intObsHt, intObsDst, ObsTurnDst As Integer

Open "C:\Airport\Runway.dat" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
Line Input #1, InputData ' Read line of data.
Debug.Print InputData ' for debugging - Print to the Immediate
window.
strIATA = Mid(InputData, 3, 4) ' this is the primary key - IATA
code is unique
strApt = Mid(InputData, 15, 18)
strCity = Mid(InputData, 35, 18)
strCntry = Mid(InputData, 61, 12)
intElev = Val(Mid(InputData, 54, 4))
intNbrOfRwys = Val(Mid(InputData, 8, 2))

'TODO Write the airport data to a record in the airport table

mySQL = "INSERT INTO Airport (IATACode, AirportName, CityName,
Country, Elevation, NumberOfRunways )"
mySQL = mySQL + " VALUES ('" & strIATA & "', """ & strApt & """,
"""
& strCity & """, '" & strCntry & "',"
mySQL = mySQL + "" & intElev & ", " & intNbrOfRwys & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

For x = 1 To intNbrOfRwys
Line Input #1, InputData
Debug.Print InputData
strRwy = Mid(InputData, 7, 7)
intSrfc = Val(Mid(InputData, 14, 1))
intTORA = Val(Mid(InputData, 15, 5))
intASDA = Val(Mid(InputData, 21, 5))
intTODA = Val(Mid(InputData, 27, 5))
intLDA = Val(Mid(InputData, 33, 5))
dblSlope = Val(Mid(InputData, 40, 5))
intLUDst = Val(Mid(InputData, 46, 4))
intNbrOfObs = Val(Mid(InputData, 53, 2))
intMonth = Val(Mid(InputData, 61, 2))
intDay = Val(Mid(InputData, 63, 2))
intYear = Val(Mid(InputData, 65, 4))

strDate = Str(intDay) & "/" & Str(intMonth) & "/" &
Str(intYear)

'TODO Write runway data to a record in the runway table

mySQL = "INSERT INTO Runway ([Date], RunwayDesignator,
SurfaceIndicator, TORA, ASDA, TODA, LDA,"
mySQL = mySQL + " Slope, LineUpDistance, NumberOfObstacles,
IATACode)"
mySQL = mySQL + " VALUES ('" & strDate & "', """ & strRwy &
""",
" & intSrfc & ", " & intTORA & ","
mySQL = mySQL + "" & intASDA & ", " & intTODA & ", " & intLDA &
", " & dblSlope & ","
mySQL = mySQL + "" & intLUDst & ", " & intNbrOfObs & ", '" &
strIATA & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

All works fine to here. The Airport and Runway tables populate perfectly.
The runway table has a field called RunwayID (Primary Key - autonumber
type)
that I want to put into the Obstacle table as well to be a foreign key, so
I
thought I would build mySQL2 and put it in the mySQL string below. Since
the
record has just been written to the Runway table above, a SELECT should
give
me RunwayID where IATACode and RunwayDesignator are equal to the strings
values just written to the runway table. I've tried numerous variations
and
just cannot get the syntax right.
Any and all help much appreciated.

mySQL2 = "SELECT RunwayID FROM Runway WHERE IATACode = '" &
strIATA & "'"
mySQL2 = mySQL2 + " And RunwayDesignator = """ & strRwy & """"

For y = 1 To intNbrOfObs
Line Input #1, InputData
Debug.Print InputData
intObsHt = Val(Mid(InputData, 42, 4))
intObsDst = Val(Mid(InputData, 47, 5))
intObsTurnDst = Val(Mid(InputData, 53, 5))

'TODO Write obstacle data to a record in the obstacle table

mySQL = "INSERT INTO Obstacle (Height, Distance,
TurnDistance, RunwayDesignator, IATACode, RunwayID)"
mySQL = mySQL + " VALUES(" & intObsHt & ", " & intObsDst &
", " & intObsTurnDst & ","
mySQL = mySQL + """" & strRwy & """, '" & strIATA & "', " &
mySQL2 & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
Next y
Next x

Loop
Close #1 ' Close file.

End Sub
Click to expand...
 
G

Guest

Smartin
Here is what mySQL2 looks like copied from the immediate window:
SELECT RunwayID FROM Runway WHERE IATACode = 'OTM ' And RunwayDesignator =
"04 "
Are the quotes the the problem?
Cam
 
S

Smartin

For the sake of consistency I'd use all one flavor or the other of
quotes. FWIW I do not find that mixing quotes actually causes a problem
though.

One potential issue is the spaces padding the values. This could be
confusing.

I'm not seeing any glaring error. Could be tired eyes.

If you were to run the code, stop at the breakpoint, and run the SQL
exactly as you pasted below in a query window, does it run? Does it so
what you expect?
 
G

Guest

If you haven't already figured it out, I'm fairly new to all this.
Could you lead me thru the running the of the SQL in a query window and how
to determine the result.
Thanks
 
S

Smartin

No problem.

You found your way through my previous suggestion of debugging and
posting the result.

So, once you have the string in the Immediate view, copy it, switch to
the database (Alt F11), go to Queries, New, Design View, don't select
any tables (click Cancel), go to the SQL view (View | SQL) and paste.
Run it (Query | Run).

What we're aiming to do is determine whether the query (a) is valid and
(b) returns expected results.

Helps?
 
G

Guest

Something positive to report.
Query appears to be valid as it does return expected result.
Now the problem seems to be sticking mySQL2 at the end of the last mySQL in
the correct format.
Here's my code for the last mySQL-

'TODO Write obstacle data to a record in the obstacle table

mySQL = "INSERT INTO Obstacle (Height, Distance,
TurnDistance, RunwayDesignator, IATACode, RunwayID)"
mySQL = mySQL + " VALUES(" & intObsHt & ", " & intObsDst &
", " & intObsTurnDst & ","
mySQL = mySQL + """" & strRwy & """, '" & strIATA & "', " &
mySQL2 & ")"
Debug.Print mySQL

and here is what the Debug.Print produces-

INSERT INTO Obstacle (Height, Distance, TurnDistance, RunwayDesignator,
IATACode, RunwayID) VALUES(12, 100, 0,"04 ", 'OTM ', SELECT RunwayID FROM
Runway WHERE IATACode = 'OTM ' And RunwayDesignator = "04 ")
 
S

Smartin

So you're saying mySQL doesn't run? You could try enclosing the subquery
(mySQL2) in parens, so the final query has

....
'OTM ', (SELECT RunwayID FROM Runway WHERE IATACode = 'OTM ' And
RunwayDesignator = "04 "))

Alternatively you could derive the value obtained by the subquery mySQL2
in code, store the result in a Variant, and concat the value itself into
mySQL. This would simplify the INSERT statement.

myRunwayID_Variant = DBEngine(0)(0).OpenRecordset(mySQL2).Fields(0).Value

(The above returns the value in the first field of the first record of
the query result.)

Then you insert the value of myRunwayID_Variant in mySQL instead of the
SELECT statement.
 
S

Smartin

Simmer2 said:
Fantastic! All works perfectly now.
Sticking RunwayID into a vartiable was by far the easiest way to go. ( now I
know how to do that).
Thank you so much for all your help.
Not only did I get my problem fixed but I learned a great deal in the
process. A great learning experience.
Again, many thanks.
Click to expand...

Best news I've heard today. Good job!
 
Top