SQL in VBA Access 2010

[Zach Lau]

I've been racking my brains over this for the better part of today. For the live of me I cannot get to the bottom of this.

I have a sub called Export. For the file naming, I must pull information from tables. The code below is a small part of the function, but it continues to return a blank query.

Sub Export(tableexportname As String)

MsgBox tableexportname
SQLlowgrade = "SELECT [" & tableexportname & "].* INTO [templow] FROM [" & tableexportname & "];"
MsgBox SQLlowgrade
SQLhighgrade = "SELECT [" & tableexportname & "].* INTO [temphigh] FROM [" & tableexportname & "];"
MsgBox SQLhighgrade

CurrentDb.Execute SQLlowgrade
CurrentDb.Execute SQLhighgrade

End Sub

Call Export ("Running_Import_LA")

MsgBox tableexportname gives me, exactly, Running_Import_LA so the variable is passing into the sub.

MsgBox SQLlowgrade gives me, exactly, SELECT [Running_Import_LA.* INTO [templow] FROM [Running_Import_LA];

This is a good query as I have tested it in the Query design and it does indeed return the full table [Running_Import_LA] into a newly created table [templow].

The same goes for MsgBox SQLhighgrade.

When I run the vba code, the query in CurrentDB.Execute returns an empty table for [templow]. I'm so stuck at this point.

Can someone please help?

 

[boblarson]

Add dbFailOnError to your code so it will tell you what is happening if it fails.

CurrentDb.Execute SQLlowgrade, dbFailOnError
CurrentDb.Execute SQLhighgrade, dbFailOnError

 

[Zach Lau]

I actually did that. No errors and 0 records are being transferred. I'm at my whits end.

With CurrentDb
.Execute SQLlowgrade, dbFailOnError
MsgBox "Low records inserted: " & .RecordsAffected
.Execute SQLhighgrade, dbFailOnError
MsgBox "High records inserted: " & .RecordsAffected
End With

 

[boblarson]

Try modifying to this:

Dim db As DAO.Database

Set db = CurrentDb

And then use db instead of CurrentDb for the execution.

 

[Zach Lau]

Ok, so I tried that. I'm still getting the same thing and I did delete the temp tables before executing the code. Any other idea? I cannot for the life of me figure out why the query executes in the wizard, but not here. Thanks.

 

[boblarson]

Two things come to mind.

1. You appear to be putting the same data to two different tables. That doesn't seem right.

2. Running_Import_LA is a table?

 

[Zach Lau]

1. Well, I'm attempting to break down a larger issue. The code will eventually sort the records then select the Top 1 into lowgrade. Then it will sort desc and select the top record into high grade. I'm then doing a DLookup to get the highest and lowest grade level in a table of student state test scores for for multiple grades.

2. Yes this is a table.

 

[Zach Lau]

This is for a file naming convention.

 

[boblarson]

Okay, I just tried using your info somewhat and it worked fine for me.

Function QRY1(tableexportname As String)
Dim strSQL As String
strSQL = "SELECT [" & tableexportname & "].* INTO [temphigh] FROM [" & tableexportname & "];"
CurrentDb.Execute strSQL, dbFailOnError
End Function

And I passed this to it (my Employees Table from the Northwind Database):
Qry1 "Employees"

And it created the table just fine.

 

[Zach Lau]

Ok, I figured that part out. I was calling an empty table earlier in the code. My apologies. I've been looking at this all day and it was running May I ask one more question? Is this the correct statement that I am looking for?

SQLlowgrade = "SELECT TOP 1 [" & tableexportname & "].* INTO [templow] FROM (SELECT * FROM [" & tableexportname & "] ORDER BY [" & tableexportname & "].[Grade of Assessment])"

I'm trying to sort the original table and select the top one. Something like this was working earlier, but I seem to have lost part of the statement. Sorry