I have an application that uses an access 2000 database currently in distribution. I need to update one of the recordsets with additional fields on my customer's computers. My data controls work fine as I have them set to connect in access 2000 format but when I try to open the database in code, I get an unrecognized data format error. What is the best way to replace or add to the database on their machines? Any help is greatly appreciated.
A:
It is possible to update an Access database using VBScript, ADO and DDL.
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Example.mdb;" _
& "Jet OLEDB:Database Password=pass;"
Set cn=CreateObject("ADODB.Connection")
cn.Open strCon
strSQL="ALTER TABLE Example ADD COLUMN Example Text (20)"
cn.Execute strSQL
More connections strings: www.connectionstrings.com
Remou
2010-05-19 21:50:46
A:
I much prefer using DAO collections to updating BE database schemas as it gives you much more control over what you can do. For example you can easily delete or create tables, records, indexes and relationships. See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app and has sample code to get you started.
Tony Toews
2010-05-20 00:48:46
The advantage of VBScript is that it is very small and easy to transfer (it can even be cut-and-pasted), can be written quickly, and will work on a site that does not have Access installed, only the runtime. You can use DAO with VBScipt, if you prefer.
Remou
2010-05-20 08:57:07
I'd much prefer the code to be within the application rather than in a separate file. I also prefer using DAO as it has a lot more control of an Access database file. Although that's likely not significant for this particular change of just adding some fields to a table.
Tony Toews
2010-05-20 21:52:14
Isn't DAO installed with Jet 4 on all copies of Windows? Thus, is it not the case that you can use DAO within vbScript?
David-W-Fenton
2010-05-20 23:25:52
I've double checked and Jet 4.0 is installed on all versions of Windows from 2000 and newer. I'm pretty sure the DAO DLLs as well.
Tony Toews
2010-05-22 18:58:12