VBA, Make table query 1

[MrHeynow]

Have this bit of code I lifted from a older post (thanks Ed2020) in which I have modified to do my bidding however I cannot get it to complete. L The scenario is this , I have a table of 30 lines called, Ar_Code, with two fields of arrier_name and ar_code . I desire a VBA solution that moves through a make table query, call it Arrier_Open, that the criteria is for each ar_code, and then naming the resulting new table the respective arrier_name.

Now I think the code I have is very close to what I need however I get the following error when stepping through the function and near the end .

Run-time error '3067':
Query input must contain at least one table or query.

Perhaps someone more familiar or with expert powers can see the issue and suggest the solution. I appreciate any suggestion.

Existing code

Public Sub MakeOpenTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String

Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT Ar_Code.Arrier_Nbr, Ar_Code.Carrier_Name FROM Ar_Code;", dbOpenSnapshot)

With rstCriteria
Do While Not .EOF
strSQL = "SELECT Arrier_Open1.* INTO tbl" & !Carrier_Name & " FROM Ar_Code WHERE (((Ar_Code)=" & """" & !Arrier_Name & """" & "));"
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing


Sorry may have posted my question in the wrong forum earlier

 

[sxschech]

When the program halts with an error, go to the immediate window and type

? rstCriteria

Also

? strSQL

to see what is shown for those items.

Perhaps you could try copying and pasting the sql statements into a query to see if an error happens there too and that may help to track down where the problem is.

 

[MrHeynow]

Here is where I , we are

Public Sub MakeOpenTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String

Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT Ar_Code.Arrier_Nbr, Ar_Code.arrier_Name FROM Ar_Code;", dbOpenSnapshot)

With rstCriteria
Do While Not .EOF
strSQL = "SELECT Arrier_Open1.* INTO [tbl" & !Arrier_Name & "] FROM Ar_Code WHERE (((Ar_Code)=" & """" & !Arrier_Nbr & """" & "));"
Debug.Print strSQL
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing

End Sub

And in the immediate window it displays this :

SELECT Arrier_Open1.* INTO [tblAm Buckets] FROM Ar_Code WHERE (((Ar_Code)="1"));

I’m getting this error message “Microsoft’s jet database engine does not recognize ‘arrier_open1*’ as a valid field name or
Expression” and is displayed when I step to ThisDB.Execute strSQL, dbFailOnError

I was thinking that SELECT Arrier_Open1.* essentially would take the all the fields within Arrier_Open1 in making a the new desired table limited by the Ar_Code? There are 20 fields within Arrier_Open1.and desired in the created tables.

Any more suggestions?

 

[sxschech]

Do you also get an error if you copy and paste the sql statement displayed in the immediate window into a new query?

 

[MrHeynow]

Yes, it does

 

[sxschech]

What happens if you view the query rather than running it?
(Open first in design view and then click on View rather than Run)
Or alternatively, change the query from make table to Select.

 

[MrHeynow]

sxschech

Maybe thats part of the issue, this query does not exist absent the VBA.

 

[sxschech]

It's okay if the strSQL query doesn't exist outside of the code, it's what it is doing or referring to that is the problem. If after pasting the sql statement and viewing it doesn't show results, then try to modify the query to get it do to what you expect and then once it is working use that sql statement in your vba code (with the appropriate modifications for your variables).

 

[MrHeynow]

So I thought…

I would change ..

strSQL = "SELECT Arrier_Open1.* INTO [tbl" & !Arrier_Name & "] FROM Arrier_Open1 WHERE (((Ar_Code)=" & """" & !Arrier_Nbr & """" & "));"

Which immdediate’s to

SELECT Arrier_Open1.* INTO [tblAm Buckets] FROM Arrier_Open1 WHERE (((Ar_Code)="1"));

To

strSQL = "SELECT Arrier_Open1.* INTO [tbl" & !Arrier_Name & "] FROM Arrier_Open1 WHERE (((Arrier_nbr)=" & """" & !Arrier_Nbr & """" & "));"

which immediates to

SELECT Arrier_Open1.* INTO [tblAm Buckets] FROM Arrier_Open1 WHERE (((Arrier_nbr)="1"));

Which really looked like I was getting somewhere and this errors with Data type mismatch in criteria expression. I checked both tables and the field of Arrier_nbr are in fact numbers.

So I then modified

strSQL = "SELECT Arrier_Open1.* INTO [tbl" & !Arrier_Name & "] FROM Arrier_Open1 WHERE (((Arrier_nbr)=" & """" & !Arrier_Nbr & """" & "));"
removing two of the double quotes from before and after in this section )=" & """" & !Arrier_Nbr & """" & ") and that seemed to work

Here is the working code !….

Public Sub MakeOpenTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String

Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT Ar_Code.Arrier_Nbr, Ar_Code.arrier_Name FROM Ar_Code;", dbOpenSnapshot)

With rstCriteria
Do While Not .EOF
strSQL = "SELECT Arrier_Open1.* INTO [tbl" & !Arrier_Name & "] FROM Arrier_Open1 WHERE (((Arrier_nbr)=" & "" & !Arrier_Nbr & "" & "));"
Debug.Print strSQL
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing

End Sub

Very Sweet , but how can I get a macro to recognize the code of MakeOpenTables() Do I need to name it a specific way ?

sxschech , I really appreciate your time, effort and skill.

 

[sxschech]

Glad to help. As for your last question, someone else will need to assist as I haven't used macros, so not familiar with integrating between vba and macro.