Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Access query vs. Excel query | Excel Programming | |||
Excel Access Query | Excel Programming |