Prev: trying to find the previous thursday before running a macro
Next: Public Variables
From: deeds on 8 Apr 2010 10:39
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.
From: Mike on 8 Apr 2010 14:02
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.
From: deeds on 14 Apr 2010 09:40
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.
 | 
Pages: 1
Prev: trying to find the previous thursday before running a macro
Next: Public Variables