INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA, Make table query

VBA, Make table query

(OP)
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
 

RE: VBA, Make table query

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.

RE: VBA, Make table query

(OP)
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?


 

RE: VBA, Make table query

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

RE: VBA, Make table query

(OP)
Yes, it does  

RE: VBA, Make table query

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.

RE: VBA, Make table query

(OP)
sxschech

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

RE: VBA, Make table query

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).

RE: VBA, Make table query

(OP)
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.


 

 

RE: VBA, Make table query

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close