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

 

[Andrzejek]

You have 30 records in your Ar_Code table and you want to create 30 tables? So if you would have 30 000 records, you would create 30 000 tables?

From your code:

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

Could you show what the BLUE line of code shows you in the Immediate Window?

BTW - You are at the wrong Forum, this is not VBA, this is 'calssic' VB 5 & 6

Have fun.

---- Andy

 

[Golom]

Your generated SQL looks a bit funny

You are selecting all fields from a table or query called "Arrier_Open1" but your FROM clause specifies a source table named "Ar_Code". Any and all tables referenced in the SQL MUST be defined in the FROM clause.

[red]!Arrier_Name & """"[/red] resolves to
[red]value in the field Arrier_Name ""[/red] WHERE conditions must evaluate to TRUE or FALSE.

You are missing a logical operator (AND, OR, XOR, etc.) between your WHERE clauses.

I also notice that
- You populate the recordset with a field "Carrier_Name" but your generated SQL references "Arrier_Name". Just a typo?

- Your generated SQL references a field "Ar_Code" which is the same as the table name. That's not illegal but is there really a field "Ar_Code" in table "Ar_Code"?

Perhaps you might try

strSQL = "SELECT Ar_Code.* INTO tbl" & !Carrier_Name & _
" FROM Ar_Code WHERE Trim$(Ar_Code & '') = '' AND &_
                     Trim$(Arrier_Name & '') = '' ;"

Concatenating the blank field '' in the WHERE clause just takes care of possibly NULL fields. A NULL will not evaluate as equal to an empty string.

 

[MrHeynow]

Andrzejek, yes thirty new tables and Immediate window shows

: strSQL : "SELECT Arrier_Open1.* INTO tblAm Buckets FROM Ar_Code WHERE (((Ar_Code)="Am Buckets"));" : String

when I pasted in your debug.print strSQL statement.

Lastly Andrzejek, I am trying to build this in Access 2000 and I found and the lifted code 12 pages in to my search results. It was the closest to what I am attempting to do. I apologize if I posted it to the wrong forum and oh yes I'm having fun.

 

[gmmastros]

The problem is... you have a column with a space in the name, therefore you are trying to create a table with a space in the name.

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

To create a table with a space in the name, you should use square brackets, like this:

   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

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrHeynow]

Golom, Your questions I'm sure are spot on however I lack the expertise and knowledge to answer some of your questions or comments.

But I'm going to try here

You are selecting all fields from a table or query called "Arrier_Open1" but your FROM clause specifies a source table named "Ar_Code". Any and all tables referenced in the SQL MUST be defined in the FROM clause.

Essentially I want use the each Arrier_Nbr as criteria from Ar.code table in a make table query which uses Arrier_Open1 table which has an Arrier_Nbr field to be limited by the criteria.

And yes a typo I was trying hide some identifiers. I will try some the suggestions with in responses and report.

 

[Andrzejek]

I would suggest to avoid spaces in table names and in field names. They are just a headache. Not that they are illegal, just a headache, IMHO.

Plus, I would suggest to reconsider creating 30 tables that are actually the same with the same structure. Bad idea, again – just IMHO.


Have fun.

---- Andy

 

[BobRodes]

If you can't change the table names, just use the Replace function to replace spaces with underline characters in your Carrier_Name field.

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[MrHeynow]

Thanks so much everyone! 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?

 

[Andrzejek]

Try to change your SQL to something like:

SELECT [red]*[/red] INTO [tblAm[red]_[/red]Buckets] FROM Ar_Code WHERE (((Ar_Code)=[red]'[/red]1[red]'[/red]));

Have fun.

---- Andy

 

[Golom]

You seem to have some confusion about the source of the data you are retrieving.

[blue]SELECT Arrier_Open1.*[/blue] is an instruction to select all fields from the table Arrier_Open1.

[blue]FROM Ar_Code[/blue] specifies the name of a table from which fields are to be selected.

Hence the conflict I mentioned ... you are attempting to retrieve fields from a table that is not defined in the FROM clause.

If you use

SELECT * INTO [tblAm_Buckets] FROM [red]Ar_Code[/red] ...

then fields will be selected from the table Ar_Code.

If you use

SELECT * INTO [tblAm_Buckets] FROM [red]Arrier_Open1[/red] ...

then fields are selected from the table Arrier_Open1

Which one do you want?

 

[MrHeynow]

Golom, you are patient, thank you.

SELECT Arrier_Open1.* is an instruction to select all fields from the table Arrier_Open1.

Correct, selecting the table Arrier_open1, as the table housing the fields I want used in making the new table. Which is to be limited by the Arrier_Nbr and produce the new table named by the arrier_name which are both fields that are needed and reside in the Ar_code table.

So I changed

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

To

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

Which does error “too few parameters. Expected 1” I guess that the WHERE statement now isnt correct as it is not limited to the rstCriteria or the specific Arrier_nbr from Ar_code.

 

[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

Sweet!!, one last thing how can I get the macro to recognize the code of MakeOpenTables() Do I need to name a specific way ?

Golom , Dude or Dudette I really appreciate your skills and style.

 

[dilettante]

VBA issues belong in forum707