Run make table query from Excel with Access closed
 

I have an make table query in Access that I want to be able to run from
Excel. Ultimately I would like to be able to run the make table with Access
closed and no messages appearing until the table is made...then a notice
saying the make table is complete. Is there any way to run a make table
access query from Excel without opening Access? Thanks.

Run make table query from Excel with Access closed
 

Kick this around. Should help
Sub makeTable()
'Needs reference the Axtive X Library 2.0 or higher
Dim cn As ADODB.Connection
Dim sCnn As String
Dim sTableName As String
Dim sMakeTableName As String

sTableName = "Table name"
sMakeTableName = "Make Table Name"

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=C:\Database.mdb"
.Open
.Execute ("SELECT " & sTableName & ".* INTO " & sMakeTableName & "
FROM " & sTableName & "")
.Close
End With
Set cn = Nothing

End Sub

"deeds" wrote:

I have an make table query in Access that I want to be able to run from
Excel. Ultimately I would like to be able to run the make table with Access
closed and no messages appearing until the table is made...then a notice
saying the make table is complete. Is there any way to run a make table
access query from Excel without opening Access? Thanks.

Run make table query from Excel with Access closed
 

Thanks...however, it is stopping at: Dim cn As ADODB.Connection any ideas?
Thanks again.

"Mike" wrote:

Kick this around. Should help
Sub makeTable()
'Needs reference the Axtive X Library 2.0 or higher
Dim cn As ADODB.Connection
Dim sCnn As String
Dim sTableName As String
Dim sMakeTableName As String

sTableName = "Table name"
sMakeTableName = "Make Table Name"

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=C:\Database.mdb"
.Open
.Execute ("SELECT " & sTableName & ".* INTO " & sMakeTableName & "
FROM " & sTableName & "")
.Close
End With
Set cn = Nothing

End Sub

"deeds" wrote:

I have an make table query in Access that I want to be able to run from
Excel. Ultimately I would like to be able to run the make table with Access
closed and no messages appearing until the table is made...then a notice
saying the make table is complete. Is there any way to run a make table
access query from Excel without opening Access? Thanks.