Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Version control for Microsoft Access development

Status
Not open for further replies.

drdexter33

Programmer
Jun 11, 2003
17
US
I recently saw this post that shows a script that is supposed to decompose a MS Access application into its constituent parts (VBA Scripts, et cetera):


Using this article, I wrote a .vbs script from the example in that post to decompose a .mdb MS Access file.

Problem is, when I run the script against the .mdb file, i get an error:

decompose.vbs(71, 5) (null): Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user.

Well, it's not missing and it's not opened by another user. And there's no lock file associated with it.

Any idea what's going wrong, or is there an easier decompose our Access database for use in our versioning software?

PS: We are using Borland Star Team.

Thanks Much!

Doug Dexter
 
Hi Doug,

I've some personal experience of this. Recently routines I've written using SaveAsText or LoadFromFile have started throwing up new errors linked to objects which are corrupted or having compiling errors - these aren't necessarily the same thing as some forms or reports which can be opened can't be exported to text. The bad news is that in extreme cases you can't open the database using shell commands.

As you've not posted your code I couldn't say how robust it is, but there are a few major gotchas with the code in your link. It couldn't open databases which have:
1. Been secured using custom workgroup files.
2. Been password protected.
3. Broken references

The last one is easily fixed, run the script from a machine where the application has been deployed. If that's not possible, check a list of references against the machine that you're running it on. If it doesn't pass muster, quit.

The first 2 can be sorted by opening the database with shell using command lines switches.

Here's how you might open a secured mdb using a VB function:

Public Function ManipulateAccessApplication(ByVal AccessFilePath, ByVal DatabaseFilePath As String, ByVal WorkgroupFilePath As String, ByVal UserName As String, ByVal Password As String)
On Error GoTo Err_ManipulateAccessApplication
Dim objAccess As Object
Dim strCommandLine As String


'Use Shell to open File or activate window if File already loaded.
strCommandLine = """" & AccessFilePath & """"
strCommandLine = strCommandLine & " """ & DatabaseFilePath & """"
strCommandLine = strCommandLine & " /wrkgrp """ & WorkgroupFilePath & """"
strCommandLine = strCommandLine & " /user """ & UserName & """"
strCommandLine = strCommandLine & " /pwd """ & UserName & """"
Shell strCommandLine, vbMaximizedFocus
'Wait for shelled process to finish.
Do
Err = 0
Set objAccess = GetObject(strDatabase)
Loop While Err <> 0

'Do something

objAccess.Quit

Exit_ManipulateAccessApplication:
Exit Function

Err_ManipulateAccessApplication:
MsgBox "ManipulateAccessApplication Error: " & Err.Number & ": " & Err.Description
Resume Exit_ManipulateAccessApplication
End Function

Converting it to VBScript shouldn't be too taxing!

The other problem with the linked script is that there's no record of what's been done. It would be useful to create a db to track attempts to decompose the file. This is probably preaching to the converted, but here goes:

The parent application record should include the master file path(s) plus a project description.

The child application object records should record:
1. Tables
2. Queries
3. Forms
4. Reports
5. Scripts (Macros)
6. Modules
7. References
8. Anything else you can think of.

The fields could include:
ObjectName (string)
ObjectType (use Access.AcObjectType enumerator)
SaveAsText (boolean)
Extension (if you're going to give your text files a custom extension)

1 to 6 can be got by opening a reference a DAO reference to the database. I'd recommend this since if the application errors when opened via shell or when looping through objects in the application interface, it might be possible to at least fail gracefully here. It's also quicker!

The object or document names can either explored by looping through DAO Container objects and their DAO Documents, or using:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects;

You'll have to add a few type conditions to this which will depend on the project but scanning the table will sort this out.

7 can be got by opening the application:
Public Function GetReferences(ByVal AccessFilePath, ByVal DatabaseFilePath As String, ByVal WorkgroupFilePath As String, ByVal UserName As String, ByVal Password As String)
On Error GoTo Err_GetReferences
Dim objAccess As Object
dim refOther As Object
Dim strCommandLine As String


'Use Shell to open File or activate window if File already loaded.
strCommandLine = """" & AccessFilePath & """"
strCommandLine = strCommandLine & " """ & DatabaseFilePath & """"
strCommandLine = strCommandLine & " /wrkgrp """ & WorkgroupFilePath & """"
strCommandLine = strCommandLine & " /user """ & UserName & """"
strCommandLine = strCommandLine & " /pwd """ & UserName & """"
Shell strCommandLine, vbMaximizedFocus
'Wait for shelled process to finish.
Do
Err = 0
Set objAccess = GetObject(strDatabase)
Loop While Err <> 0

For Each refOther In objAccess.References
'Record references
Next refOther

objAccess.Quit

Exit_GetReferences:
Exit Function

Err_GetReferences:
MsgBox "GetReferences Error: " & Err.Number & ": " & Err.Description
Resume Exit_GetReferences
End Function

Once you've got your snapshot, loop through your records attempt to save the objects to a text file. I'd break out the functionailty so the whole routine doesn't fail if you can't save the object. Here's how you might do it:

Public Function SaveAsTextFile(ByVal AccessObject As Object, ByVal ExportFolder As String, ByVal ObjectName As String, ByVal ObjectType As Long, ByVal Extension As String)
On Error GoTo Err_SaveAsTextFile
Dim strError As String
Dim lngError As Long

AccessObject.SaveAsText ObjectType, ObjectName, ExportFolder & "\" & ObjectName & "." & Extension

Exit_SaveAsTextFile:
Call RecordResult(ObjectName, lngError, strError)
Exit Function

Err_SaveAsTextFile:
lngError = Err.Number
strError = Err.Description
Resume Exit_SaveAsTextFile
End Function

RecordResult would be a function to write to results table. If lngError = 0 then the operation has been successful. In that functionality could be used everywhere.

If your objects do fail to export, the chances are that you'll have to rebuild the object.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top