Run Access query from Excel
 

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
-----Original Message-----
Hi i want to run an acces append query from excel vba, i
can open the
database and run an autoexec macro but is not very
practical as you open the
database manualy and you have to cancel the the message
asking you to confirm
if you want to append the data.
i need that line to open the query from excel.

can any one help?

thanks.
.

Run Access query from Excel
 

"nath" wrote ...

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.

--

Run Access query from Excel
 

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


"nath" wrote:

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
-----Original Message-----
Hi i want to run an acces append query from excel vba, i
can open the
database and run an autoexec macro but is not very
practical as you open the
database manualy and you have to cancel the the message
asking you to confirm
if you want to append the data.
i need that line to open the query from excel.

can any one help?

thanks.
.