Database Search
TechHome
- Access 2000 Advanced DML Transactions
- Access Recordset to CSV File UDF
- Access RunCommand Constants
- ACCESS Table or Query Exists
- Access to Excel via SQL
- Access Visible Problem
- Adding Fields to an Existing Access Table
- Advanced DML Transactions
- Advanced SQL in the Jet 4.0 Provider Example
- Another OLE with Access Example
- Automating Access Tutorial
- Check if Access Table Exists
- Commit Rollback Begin Trans Updatebatch
- Control Access Forms
- Count Access Records
- Csv to Access
- Dealing with ACCESS 97 Field Types
- execute a macro in Access
- Execute a Public function in Access
- Export Access Report in Choice of Formats
- Export MDB to Flat File
- Find Read and Insert Data in Access
- Gather ACCESS Table and Column Information Into Excel
- Get Access Version That Created MDB File
- Get Column Data From an Access Database
- Get Record Count
- How to Use the OpenTable method
- Launch Access Macro from Commandline
- Loop thru a recordset in Access
- Office 2003 Macro Security Issue
- OLE and Access
- OLE Import a table into Access
- OLE Import Table into Access
- Open Access DB with DAO
- Pack an Access Database
- Query and Export Data from Microsoft Access
- Read Write Delete Tables from Access DB
- Repair MDB
- Retrieve Data in Access
- SQL UPDATE Fails with Access
- UDF to Return Record Count
- Updating Table From Filtered Recordset
- Ways to Get Data from and Access file without Access installed
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Ways to Get Data from and Access file without Access installed
Question:
I'm just getting started with trying to talk to an Access database from Winbatch and I'm getting confused between DAO, ADO, OLE and ODBC. I'm not sure where to start.All I need to do is open an Access database ("dvd.mdb"), remove a record from a table ("TblDVD") where the fieldname ("Title") matches a variable I have, and then close the database.
The machine doesn't have Access installed, but it does have DAO.DBEngine.35 referenced in the registry.
I found this line in the Tech Database, but I'm not sure how to set up the supporting code:
db.execute ("delete from [table] where [field] = 'xyz'")
Suggestions?
Answer:
Since the machine doesn't have Access installed you will want to make sure MDAC is installed:http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/MDAC+MDAC~Installation~Details.txt To begin with try searching our tech support database for the keywords "ADO" or "DAO", you'll find lots of sample code.
You code might look something like this:
; DAO is similar to MS Access Basic...
MDBfile = "c:\data\access\survey template.mdb"
objdao = ObjectOpen("DAO.DBEngine.35") ;<-- you may have to change for your version
objws = objdao.CreateWorkspace("JetWorkspace", "admin", "")
objdb = objws.OpenDatabase( MDBfile )
sqlstr = "" ;<-- create the delete SQL statement...
objdb.execute(sqlstr) ;<-- execute it...
ObjectClose(objdb)
ObjectClose(objws)
ObjectClose(objdao)
Message("Debug", "End of Job")
Exit
Referenced article: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO+Delete~All~Records~from~Access~Table.txt
User Reply:
Thanks for the help. With minor tweaking I got this to work:
dao = objectopen("DAO.DBEngine.36")
ws = dao.CreateWorkspace("JetWorkspace", "admin", "")
db = ws.opendatabase("c:\db1.mdb")
db.execute ("delete from [table1] where [name] = 'test1'")
objectclose(db)
objectclose(ws)
objectclose(dao)
message("Debug", "End of Job")
exit
My next challenge is to open the .mdb file and read the contents of a field into either an array or out to a text file that
I can then easily manipulate in Winbatch. I was looking at a "SELECT * INTO..." type command, but I can't find enough
information to get it to work. I need to either dump the entire contents of the field to a text file or the code to loop
through each record value and copy it to an array.
Where can I get more information on the DAO object structure? I'm not having much luck at the Microsoft site. I hate having to ask these dumb questions.
Answer:
Open an Access database.Click Modules under Objects, and then click New.
In the Visual Basic Editor, click References on the Tools menu.
Scroll through the list until you find Microsoft DAO 3.6 Object Library, and then click to select the reference.
Click OK.
You should now be able to search the DAO object model.
Article ID: W16601
File Created: 2014:07:18:09:51:38
Last Updated: 2014:07:18:09:51:38