make table from query

[Access Beginner]

Hi All,

I found this bit of code when I did a search here for VBA to make table from a query

Private Sub doit()

DoCmd.SetWarnings False
DoCmd.OpenQuery "test"
DoCmd.SetWarnings True

End Sub

But when I run this it just opens the query, should there be something else to turn the query into a table.

Please forgive my ignorance, I'm still on a steep learning curve.

Using Access 2007

 

[dk]

Hi

That will only make a table if your query "test" is a make-table query. Is that the case? You specify a make-table query by going into query design view and selecting the "Make Table" icon.

HTH
DK

 

[Access Beginner]

Hi

Thanks for your reply.

No the query "test"is not a make-table query.

What I was hoping to find was VBA, that would turn a query into a table for me. This query is based on another query and by making it a table, I hope to speed the linking to an Excel spreadsheet up.

In way of explaining. I have 20 files ( some have over 2 million records) and I create a number of queries based on these tables. These files are weekly. So I was hoping to have this query "test" as table, this way the query would only be run once, and I can then link my Excel spreadsheets to this newly created table. Once new data arrives, I can than run this VBA again and start the process over again.

The query "test"is also the base of a few other queries as well, so this will help me greatly.

I hope I have been clear.

Cheers

 

[dk]

This code will create a table based on the specified query and will delete the table if it already exists. Therefore, please use a copy of your database to test this

Sub CreateTableFromQuery()

    Dim sQueryName As String
    Dim sNewTableName As String
    Dim sSQL As String

    On Error GoTo ErrHandler

    'Change these to suit your requirements
    sNewTableName = "MY_NEW_TABLE"
    sQueryName = "TEST"


    On Error Resume Next
    sSQL = "DROP TABLE " & sNewTableName
    CurrentDb.Execute sSQL
    
    If Err.Number <> 0 Then
        MsgBox "Could not delete table " & sNewTableName & vbCr & Err.Description
    Else
        On Error GoTo ErrHandler

        'Create the table based on records from sQueryName
        sSQL = "SELECT * INTO " & sNewTableName & " FROM " & sQueryName

        CurrentDb.Execute sSQL
    End If

    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation, "Error"

End Sub

Does that do what you want?

HTH
DK

 

[Access Beginner]

Thanks for your help.

I will test this tomorrow at work and let you know. I dont have any actual files at home to test it with.

Thanks again, your assistance is much appreciated.

Cheers