Run Make Table Query in a closed database

[villy]

Hello Excel Gurus,

I have run a query from excel macro but only one thing is that I created a make table query and I need to run it without opening the database. I know it is possible I also found some codes regarding this issue but nothing's really answer my needs.

What I want is to run make table query through a closed database.
Thanks in advance for all your help.

Regards,

Villy

 

[dk]

Hi

What database are you using? You can typically connect to a database using something like ActiveX Data Objects (ADO) or Data Access Objects (DAO). Once connected you can then run any SQL you need including make table queries. Here is an example using ADO to connect to an Access mdb file and then running a make table query:

Sub ConnectExample()

    Dim oCN As Object
    
    Set oCN = CreateObject("ADODB.Connection")

    oCN.Provider = "Microsoft.JET.OLEDB.4.0"

    oCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=c:\your folder\your file.mdb;"


    oCN.Execute "SELECT * INTO DESTINATION_TABLE FROM SOURCE_TABLE"
    oCN.Close

    Set oCN = Nothing

End Sub

HTH
DK

 

[villy]

I am using Access database 2003.
With your code can I run a make table query with a closed database?
Thanks

 

[dk]

You do not need to have the Access file open in order to run code like this, and yes, you can run make-table queries using this technique.

 

[villy]

Ok let me try then give you a feedback.
Thanks

 

[energman58]

This thread has loads of information which should let you do what you want - it certainly helped me!

http://www.mrexcel.com/forum/showthread.php?t=558247

 

[villy]

great link let me try them when I log into the network right now I am not able to as I am in the remote...
Thanks anyway.
Feedback after..

 

[villy]

Hi

What database are you using? You can typically connect to a database using something like ActiveX Data Objects (ADO) or Data Access Objects (DAO). Once connected you can then run any SQL you need including make table queries. Here is an example using ADO to connect to an Access mdb file and then running a make table query:

Sub ConnectExample()
 
    Dim oCN As Object
 
    Set oCN = CreateObject("ADODB.Connection")
 
    oCN.Provider = "Microsoft.JET.OLEDB.4.0"
 
    oCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=c:\your folder\your file.mdb;"
 
 
    oCN.Execute "SELECT * INTO DESTINATION_TABLE FROM SOURCE_TABLE"
    oCN.Close
 
    Set oCN = Nothing
 
End Sub

HTH
DK

hi there..
I substituted my sql but gives me an erro propertext undefined..Any thoughts?
Thanks

 

[dk]

You will also need to change the connection string from C:\your folder\blah blah to wherever your database is located. Have you done that too?

Regarding your error message, what is the exact error message, do you get it if you try and compile the code (choose Debug, Compile from the VB Editor window) or is it an error that occurs when you run the query?

 

[villy]

I changed the location the sql run compile debug... then the error comes up in a query.. one mvp says because of 'propertext' or 'propervalue' function cannot be run into excel.. what can I substitute to it to make it run?
Or any thoughts?