As a long-time AS400 software developer but beginning ACCESS developer, I am struggling with the Development Environment on ACCESS. My Standard Procedure when modifying an existing/in use application, would be to copy the programs to a Test Environment, write a conversion for the new/changed files (if necessary) and replace/convert the files as the last step to minimize Production losing any time or data appplied while the Application was being modified. Since ACCESS tables are part and parcel of the .mdb, how would I set up a test environment. I have recently been given responsibilty for several large existing ACCESS applications, and the only way I can see to make modifications is to copy the .mdb to a Test Folder. Figure out what needs to be done and test, keeping track of each step and then go back into the Production .mdb, make all of the previously determined changes and hope you remembered them all. There has to be a better way!
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 bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ACCESS Development/Test Environment? 4
- Thread starter PaulaJ
- Start date
-
1
- #2
BlackKnight
Programmer
Hi,
Normally, you would have an Access db for your tables (data) and an Access db for your everything else like code, forms, reports etc. This way you can test and modify your code all that you want without preventing users from using your last build. Once the new build has been tested and is ready to implement simply overwrite the users' build with your latest one. This method allows you to create and preserve test data in multiple dbs without having to recreate it.
You can link the code db to the data db by linking the tables. There are some other methods but this one is quick and easy. to do.
Have a good one!
BK
Normally, you would have an Access db for your tables (data) and an Access db for your everything else like code, forms, reports etc. This way you can test and modify your code all that you want without preventing users from using your last build. Once the new build has been tested and is ready to implement simply overwrite the users' build with your latest one. This method allows you to create and preserve test data in multiple dbs without having to recreate it.
You can link the code db to the data db by linking the tables. There are some other methods but this one is quick and easy. to do.
Have a good one!
BK
- Thread starter
- #3
-
1
- #4
Paula,
You can separate your tables from the rest of your database structure by simply copying your queries, forms, reports, modeles to a new .mdb file. It's as easy as right clicking and copying and pasting into the new DB. The new DB just asks you to name it when pasting it. Give it the same name obviously. When you have all of your structure in place, go to file, get external data, and link tables. From there, you can select your old database and link all of your tables back to your new file. Additionally, you can copy the tables from one .mdb file to another using the same method, or by exporting to a different file and importing into your new database.
Good luck,
V
You can separate your tables from the rest of your database structure by simply copying your queries, forms, reports, modeles to a new .mdb file. It's as easy as right clicking and copying and pasting into the new DB. The new DB just asks you to name it when pasting it. Give it the same name obviously. When you have all of your structure in place, go to file, get external data, and link tables. From there, you can select your old database and link all of your tables back to your new file. Additionally, you can copy the tables from one .mdb file to another using the same method, or by exporting to a different file and importing into your new database.
Good luck,
V
-
1
- #5
Paula:
I can definitely understand how you feel. I have been an AS/400 programmer/consultant for many years and dove into doing some VB/VBA about 2 years ago. Since you are a beginner, there is an even simpler method of doing the above.
I don't know what version you have, but in Access 2k it's Tools--Database Utilities--Database Splitter. A wizard will walk you through it. I was a little scared of this too, but since my users all ALL remote, this was the only way. It works great. If the data is in the same directory as the program mdb, then the tables should relink automatically. To be safe, you can use the following in a module to look for the data & have an autoexec macro run it:
(I got this either from an Access Book, or a good site like "The Access Web" -- unfortunately, I must have inadvertantly deleted the credits when moving code around, sorry).
Function AutoExec()
On Error GoTo AutoExec_Err:
Dim fAnswer As Boolean
'Open splash screen form
DoCmd.OpenForm "frmSplash"
DoEvents
'Invoke hourglass
DoCmd.Hourglass True
'Call routine that checks if tables are properly attached
fAnswer = AreTablesAttached()
'Test return value and proceed only if tables were
'successfully attached
If Not fAnswer Then
MsgBox "You Cannot Run This App Without Locating Data Tables"
DoCmd.Close acForm, "frmSplash"
DoCmd.Close acForm, "frmGetTables"
End If
DoCmd.Hourglass False
DoCmd.OpenForm "frmMainMenu"
Exit Function
AutoExec_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Function
End Function
Function AreTablesAttached() As Boolean
' Update connection information in attached tables.
'
' Number of attached tables for progress meter.
Const MAXTABLES = 8
Const NONEXISTENT_TABLE = 3011
Const DB_NOT_FOUND = 3024
Const ACCESS_DENIED = 3051
Const READ_ONLY_DATABASE = 3027
Dim intTableCount As Integer
Dim intResponse As Integer
Dim strFileName As String
Dim strAppDir As String
Dim vntReturnValue As Variant
Dim tdf As TableDef
Dim DB As Database
Dim rst As Recordset
Set DB = CurrentDb
AreTablesAttached = True
' Continue if attachments are broken.
On Error Resume Next
' Open attached table to see if connection information is correct.
Set rst = DB.OpenRecordset("tblCustomers"
' Exit if connection information is correct.
If Err.Number = 0 Then
rst.Close
Exit Function
Else
'Otherwise, determine location of current database
strAppDir = Left(DB.Name, LastOccurrence(DB.Name, "\")
'Try to establish the connection searching for the linked
'tables in the same folder as the application database
If TryAgain(strAppDir) Then
rst.Close
Exit Function
End If
'If connection still cannot be established, continue
'Warn the user
MsgBox "You Must Locate the Data Tables"
DoEvents
DoCmd.OpenForm FormName:="frmGetTables", WindowMode:=acHidden
Forms!frmGetTables!dlgCommon.DialogTitle = _
"Please Locate the Database Containing the Data Tables"
Forms!frmGetTables!dlgCommon.ShowOpen
strFileName = Forms!frmGetTables!dlgCommon.Filename
End If
If strFileName = "" Then
GoTo Exit_Failed ' User pressed Cancel.
End If
' Initialize progress meter.
vntReturnValue = SysCmd(SYSCMD_INITMETER, "Attaching tables", MAXTABLES)
' Loop through all tables, reattaching those with
' nonzero-length Connect strings.
intTableCount = 1 ' Initialize TableCount for status meter.
For Each tdf In DB.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = ";DATABASE=" & strFileName
Err.Number = 0
tdf.RefreshLink
If Err.Number <> 0 Then
If Err.Number = NONEXISTENT_TABLE Then
MsgBox "File '" & strFileName & _
"' does not contain required table '" & _
tdf.SourceTableName & "'", 16, "Can't Run This App"
ElseIf Err.Number = DB_NOT_FOUND Then
MsgBox "You can't run FSG Main Application " & vbCrLf & _
"Until you locate Data File", 16, "Can't Run Application"
ElseIf Err.Number = ACCESS_DENIED Then
MsgBox "Couldn't open " & strFileName & _
" because it is read-only or it is located " & _
"on a read-only share.", 16, "Can't Run This App"
ElseIf Err.Number = READ_ONLY_DATABASE Then
MsgBox "Can't reattach tables because Data File " & _
"is read-only or is located on a read-only share.", _
16, "Can't Run This App"
Else
MsgBox Error, 16, "Can't Run This App"
End If
intResponse = MsgBox(tdf.Name & " Not Found. " & _
vbCrLf & "Would You Like to Locate it?", _
vbQuestion + vbYesNo)
If intResponse = vbYes Then
Forms!frmLogon!dlgCommon.DialogTitle = "Please Locate " & _
tdf.Name
Forms!frmLogon!dlgCommon.ShowOpen
strFileName = Forms!frmLogon!dlgCommon.Filename
Else
AreTablesAttached = False
GoTo Exit_Final
End If
End If
intTableCount = intTableCount + 1
vntReturnValue = SysCmd(SYSCMD_UPDATEMETER, intTableCount)
End If
Next tdf
GoTo Exit_Final
Exit_Failed:
MsgBox "You can't run this program until " & _
"you locate Data File", 16, "Can't Run This Program"
AreTablesAttached = False
Exit_Final:
vntReturnValue = SysCmd(SYSCMD_REMOVEMETER)
End Function
Change the table name(s) to match your database.
Good Luck,
Ken Hood
I can definitely understand how you feel. I have been an AS/400 programmer/consultant for many years and dove into doing some VB/VBA about 2 years ago. Since you are a beginner, there is an even simpler method of doing the above.
I don't know what version you have, but in Access 2k it's Tools--Database Utilities--Database Splitter. A wizard will walk you through it. I was a little scared of this too, but since my users all ALL remote, this was the only way. It works great. If the data is in the same directory as the program mdb, then the tables should relink automatically. To be safe, you can use the following in a module to look for the data & have an autoexec macro run it:
(I got this either from an Access Book, or a good site like "The Access Web" -- unfortunately, I must have inadvertantly deleted the credits when moving code around, sorry).
Function AutoExec()
On Error GoTo AutoExec_Err:
Dim fAnswer As Boolean
'Open splash screen form
DoCmd.OpenForm "frmSplash"
DoEvents
'Invoke hourglass
DoCmd.Hourglass True
'Call routine that checks if tables are properly attached
fAnswer = AreTablesAttached()
'Test return value and proceed only if tables were
'successfully attached
If Not fAnswer Then
MsgBox "You Cannot Run This App Without Locating Data Tables"
DoCmd.Close acForm, "frmSplash"
DoCmd.Close acForm, "frmGetTables"
End If
DoCmd.Hourglass False
DoCmd.OpenForm "frmMainMenu"
Exit Function
AutoExec_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Function
End Function
Function AreTablesAttached() As Boolean
' Update connection information in attached tables.
'
' Number of attached tables for progress meter.
Const MAXTABLES = 8
Const NONEXISTENT_TABLE = 3011
Const DB_NOT_FOUND = 3024
Const ACCESS_DENIED = 3051
Const READ_ONLY_DATABASE = 3027
Dim intTableCount As Integer
Dim intResponse As Integer
Dim strFileName As String
Dim strAppDir As String
Dim vntReturnValue As Variant
Dim tdf As TableDef
Dim DB As Database
Dim rst As Recordset
Set DB = CurrentDb
AreTablesAttached = True
' Continue if attachments are broken.
On Error Resume Next
' Open attached table to see if connection information is correct.
Set rst = DB.OpenRecordset("tblCustomers"
' Exit if connection information is correct.
If Err.Number = 0 Then
rst.Close
Exit Function
Else
'Otherwise, determine location of current database
strAppDir = Left(DB.Name, LastOccurrence(DB.Name, "\"
)'Try to establish the connection searching for the linked
'tables in the same folder as the application database
If TryAgain(strAppDir) Then
rst.Close
Exit Function
End If
'If connection still cannot be established, continue
'Warn the user
MsgBox "You Must Locate the Data Tables"
DoEvents
DoCmd.OpenForm FormName:="frmGetTables", WindowMode:=acHidden
Forms!frmGetTables!dlgCommon.DialogTitle = _
"Please Locate the Database Containing the Data Tables"
Forms!frmGetTables!dlgCommon.ShowOpen
strFileName = Forms!frmGetTables!dlgCommon.Filename
End If
If strFileName = "" Then
GoTo Exit_Failed ' User pressed Cancel.
End If
' Initialize progress meter.
vntReturnValue = SysCmd(SYSCMD_INITMETER, "Attaching tables", MAXTABLES)
' Loop through all tables, reattaching those with
' nonzero-length Connect strings.
intTableCount = 1 ' Initialize TableCount for status meter.
For Each tdf In DB.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = ";DATABASE=" & strFileName
Err.Number = 0
tdf.RefreshLink
If Err.Number <> 0 Then
If Err.Number = NONEXISTENT_TABLE Then
MsgBox "File '" & strFileName & _
"' does not contain required table '" & _
tdf.SourceTableName & "'", 16, "Can't Run This App"
ElseIf Err.Number = DB_NOT_FOUND Then
MsgBox "You can't run FSG Main Application " & vbCrLf & _
"Until you locate Data File", 16, "Can't Run Application"
ElseIf Err.Number = ACCESS_DENIED Then
MsgBox "Couldn't open " & strFileName & _
" because it is read-only or it is located " & _
"on a read-only share.", 16, "Can't Run This App"
ElseIf Err.Number = READ_ONLY_DATABASE Then
MsgBox "Can't reattach tables because Data File " & _
"is read-only or is located on a read-only share.", _
16, "Can't Run This App"
Else
MsgBox Error, 16, "Can't Run This App"
End If
intResponse = MsgBox(tdf.Name & " Not Found. " & _
vbCrLf & "Would You Like to Locate it?", _
vbQuestion + vbYesNo)
If intResponse = vbYes Then
Forms!frmLogon!dlgCommon.DialogTitle = "Please Locate " & _
tdf.Name
Forms!frmLogon!dlgCommon.ShowOpen
strFileName = Forms!frmLogon!dlgCommon.Filename
Else
AreTablesAttached = False
GoTo Exit_Final
End If
End If
intTableCount = intTableCount + 1
vntReturnValue = SysCmd(SYSCMD_UPDATEMETER, intTableCount)
End If
Next tdf
GoTo Exit_Final
Exit_Failed:
MsgBox "You can't run this program until " & _
"you locate Data File", 16, "Can't Run This Program"
AreTablesAttached = False
Exit_Final:
vntReturnValue = SysCmd(SYSCMD_REMOVEMETER)
End Function
Change the table name(s) to match your database.
Good Luck,
Ken Hood
-
1
- #6
Presumably, your users are opening the database directly from the network file. Splitting the database has another advantage: Each user can copy the front end file to his/her workstation. When they open their local copy of the front end, they'll have all their forms, reports, macros, and code on their local machine, instead of having to load them over the network. That will speed up the application.
For development, you, too, will have a local copy of the front end, but you also need your own copy of the back end (the database file that contains the tables) so you won't be interfering with production. You need to run the Linked Table Manager (on the Tools>Add-Ins menu) to "point" your front end to your private back end's tables. After you've made your changes, run the Linked Table Manager again to "point" to the network back end, and then copy your front end to each user's workstation (or put it on the network and let them copy it themselves).
One warning, though: If your development requires changes to the tables, you'll have to take the network back end out of production so you can make the table changes manually. Also, you may have to ensure that all users have upgraded their local front end copies before you return it to production. Or, you might not have to do that; if all you've done is add tables and/or fields, and not deleted or changed any fields, the old front ends will probably work fine (provided you haven't changed any critical validation logic or something like that).
If you want to ensure your users are always using the most current version of the front end, you can create a special table in the back end that contains one row and one column, the "expected front end version number". The front end would then check this value at startup, and if it's not the same as the front end's version, show a MsgBox telling the user to copy the new front end off the network.
In Access 97, the Database Splitter is under Tools>Add-Ins.
Rick Sprague
For development, you, too, will have a local copy of the front end, but you also need your own copy of the back end (the database file that contains the tables) so you won't be interfering with production. You need to run the Linked Table Manager (on the Tools>Add-Ins menu) to "point" your front end to your private back end's tables. After you've made your changes, run the Linked Table Manager again to "point" to the network back end, and then copy your front end to each user's workstation (or put it on the network and let them copy it themselves).
One warning, though: If your development requires changes to the tables, you'll have to take the network back end out of production so you can make the table changes manually. Also, you may have to ensure that all users have upgraded their local front end copies before you return it to production. Or, you might not have to do that; if all you've done is add tables and/or fields, and not deleted or changed any fields, the old front ends will probably work fine (provided you haven't changed any critical validation logic or something like that).
If you want to ensure your users are always using the most current version of the front end, you can create a special table in the back end that contains one row and one column, the "expected front end version number". The front end would then check this value at startup, and if it's not the same as the front end's version, show a MsgBox telling the user to copy the new front end off the network.
In Access 97, the Database Splitter is under Tools>Add-Ins.
Rick Sprague
- Thread starter
- #7
- Status
Similar threads
- Locked
- Question
- Locked
- Question