Run Access query from Excel

[nath]

Yeah, it is, here goes

enable your DAO and ADO references

dim dbs as database
dim wrkjet as workspace

fname = "filename of database plus full path"

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase(fname, True)

sql = "copy this from your query builder in Acces"
dbs.execute sql
dbs.close

Hope this helps

Nath

 

[Jamie Collins]

enable your DAO and ADO references

dim dbs as database
dim wrkjet as workspace

fname = "filename of database plus full path"

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase(fname, True)

sql = "copy this from your query builder in Acces"
dbs.execute sql
dbs.close

Why reference ADO if you are not using ADO?

Jamie.

--

 

[Mark]

I'm trying to use this code and I keep getting Error 13 type missmatch on the
set dbs line - any ideas

Sub TestAccessRunQuery()
Dim dbs As Database
Dim wrkjet As workspace
fname = "\\Erbnfp4\Vol1\data\eps-s120\MANGMENT\HRO Team\HRO Team.mdb"
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkjet.OpenDatabase(fname, True)
Sql = "SELECT 'Testdata' AS Testfield INTO Testtable;"
dbs.Execute Sql
dbs.Close
End Sub

Mark