Batch updating of an Access database (DDL) . Possible?

Question

I have taken over support of a VB .Net application that makes use of an Access database. Now I discover that the developer who wrote this application did not create any unique indexes on the primary key columns for the tables in the database. So none of the primary key fields are actually flagged as being primary keys in access and so no duplicate checking is being performed by Access. Furthermore, the primary key columns are not being auto-incremented with an AutoNumber type in Access, rather the developer has used queries like this to insert rows into the tables:

INSERT INTO Users (pkid, FirstName, Surname, City) SELECT iif(isnull(max(pkid)),0,max(pkid))+1, [@strFirstName], [@strSurname], [@strCity], FROM Users;

This application is generally run as a networked application with up to 40 people using the application simultaneously, and combined with design issues already mentioned, this has resulted in serious concurrency issues with several users being assigned the same primary key value. This == BIG PROBLEM. :(

Now I need to sort this out (guess what I will be doing this weekend!! Great.). I have thought about maybe using a mutex around the code that calls the Access insert queries; not the neatest of solutions, but it would be the quickest. Ultimately though, I would like to have the database corrected and have proper primary key values in place. But the application is currently running at about 15 different sites, so I need to do some sort of batch update of the database.

I am not that familiar with Access (this is the first time that I am using Access), so sorry if this is a bit of a noob question, but I need a solution quickly. If I had this problem with Sybase or MSSql database, with which I am familiar, I would create an SQL batch that included the necessary DDL commands to correct the database. So the question is, is it possible in Access to create an SQL script that can be launched from a batch file? If not, how could I go about creating a "patch" that can be executed at the different sites that would update the respective databases.

Answer 1

Actually, you can use windows scripting. Type the following into a notepad, and then save as ADDPK.vbs.

Set dbeng = CreateObject("DAO.DBEngine.120")
strMdbFile = "C:\database53.accDB"
Set db = dbeng.OpenDatabase(strMdbFile)

strSql = "ALTER TABLE FAXBOOK add column ID COUNTER primary key"
db.Execute strSql

Then from the windows command line, you go:

C:\>c:\windows\syswow64\wscript.exe "c:\addpk.vbs"

You have use the 32 bit version here since I am assuming 32 bit Access.

If you using pre-access 2010, then use:

Set dbeng = CreateObject("DAO.DBEngine.36")
strMdbFile = "C:\database53.mdb"
Set db = dbeng.OpenDatabase(strMdbFile)
etc.

I am using the windows built in vbs scripting in above, but you can use javascriting in place of the above (just save the above file as .js).

And if you using mdb files, then the above even works on windows boxes without any software installed. In other words a clean windows xp box will run the above windows scripts without even having to install Access.

As noted, for the above JET example? JET has been installed on windows by default since windows 98SE, so the above mdb script will even work on those boxes. (even on a clean virgin install the above script will run).

For later editions and if you using accDB files, then you need the ACE data engine. This is not instaleld by default on windows - you have to install it, or you can hope/assume Access has been installed. The engine can be installed as a seperate download from Access if you wish. And there is a 64 bit version available.

So at the end of the day here, you don't need Access and you can use base windows scripting to update those databases.

Answer 2

You can run SQL easily enough in VBA, but I think it would be easiest to create an empty Access database with autoincrements in place of the current ID columns and append all your data, because you cannot change a field to autoincrement once it contains any data.

Answer 3

Access is a file-based database, so if you want to send out updates to the DDL, you send out a new .mdb.