Cannot enter design mode after DB conversion 1

[MaDmiX]

Hi All,

We have recently converted our database from Access 97 to 2000. All went relatively smoothly, but I notice that now I cannot enter design mode unless I open the database in exclusive mode. To do this I have to kick everyone off of the system (which disrupts operations).

This didn't use to be the case with Access 97. I was able to make changes to the design and they would be applied as users logged off and back on. Is there a way to get Access 2000 to work the same way?

Thanks,

Ken

 

[NorthNone]

It is a feature. Check this out:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q200290

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[MaDmiX]

Thank you NorthNone,

I don't think that we will be able to get a copy of Access 2000 Developer, so this option is out. But since I am the only developer of this app, maybe the second option will work fine... I hope.

I will experiment with keeping a local copy of my database and exporting changes to the networked copy as needed. The only problem I can forsee is with the security. Access may not let me overwrite the objects that are on the secure database, due to permissions.

Thanks again,

Ken

 

[NorthNone]

I usually split my databases front-end/back-end. Then most of my development work is in the front-end where everything except data tables reside. When my development is proved out locally, I simply overwrite the front-end database(s) on the network which delivers the changes to my users.
Caveat: in my case, all the front-ends are on the network. There are posts on how to deliver new front-ends to local machines. I've never needed to do that (so far )
HTH
JSouth
P.S. If it is user-level security, you won't have any problems exporting your objects, if that is how you decide to proceed. If all else fails, you should be able to go to the network copy and import from your local copy. Some combination of the above usually works.
P.P.S. Backup, backup, backup

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[MaDmiX]

No Luck :-(

I tried both importing and exporting the objects but I still get the same "You don't have exclusive access" error. It seems that Access 2000 sees these actions as design changes and won't allow them.

I don't really understand how the front end/back end design wil help me get around this issue, but I will look into it. In my case, the entire database is on the network and users access all of the forms from there. I read somewhere, where I can remotely prompt users to save and close the database. This might be another way to go.

Thanks again,

Ken

 

[NorthNone]

Sorry for not being clear. You still have to have exclusive access to the database to import and export objects. I didn't mean it as a workaround for that, just a way to move your changes between copies.
Good luck.

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[KenReay]

Hi

The Front End (FE) / Back End (BE) is teh way to go

in the BE you put all of the datatables

in the FE, you put everything else, eg forms, reports, queries, macros, modules etc

in the FE, you use File\getexternal data\link, to link to the tables in the BE

it is usual to also have code to automatically check for broken links, and to give the user a user friendly dialog to relink the tables if necessary

the BE is placed on a network share

the FE may be placed on a network share and one copy used by 'n' users, but it is more usual to place the FE on each users local c: drive, in this later event it is usual to introduce a mechanism to automatically download new version as required

by adopting this approach you can make your developement cahnges and do testing on a seperate copy of the FE /BE, and only after you have thoroughly tested any changed do you deploy the updated FE. If you adopt the approach of having a 'master' copy of teh current release on the server, you simply replace that with the updated version, as users come to load teh application, the update mechanism copies a new version to their c: drive and loads it for them. The automatical down load mechanism can be a simple script file, or an application (see Tony Toews site for example, search google on tony toews for URL).



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NorthNone]

Ken - would you be so kind as to post a sample of code to "...automatically check for broken links, and to give the user a user friendly dialog to relink the tables if necessary" ???
I would be most grateful
JSouth


---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[KenReay]

Hi

Public Function LinksBroken() As Boolean
Dim Db As DAO.Database
Dim tdf As DAO.TableDef
On Error GoTo Error_LinksBroken
'
LinksBroken = False
Set Db = CurrentDb()
For I = 0 To Db.TableDefs.Count - 1
Set tdf = Db.TableDefs(I)
If Len(tdf.Connect) > 0 Then
If Len(tdf.Fields(0).NAME) > 0 Then
End If
End If
Next I
Set Db = Nothing
Exit_LinksBroken:
Exit Function
Error_LinksBroken:
Select Case Err.Number
Case 3265
LinksBroken = True
Resume Exit_LinksBroken
Case Else
MsgBox "Error " & Err.Number & " " & Err.Description & " LinksBroken"
End Select
Resume Exit_LinksBroken
End Function

will check for broken links, you need a reference to DAO to use it

in addition you need a form, to prompt for file location information (ie path) and a button to relink tables so:

Dim Db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim I As Integer
Dim j As Integer
Dim k As Integer
'
On Error GoTo Err_cmdExit_Click

If Len(Trim(Nz(txtDataMDB, "") & "")) = 0 Then
MsgBox "Please Specify name of File containing the Data"
Exit Sub
Else
If Dir(txtDataMDB) = "" Then
MsgBox "Cannot find File " & txtDataMDB
Exit Sub
End If
Set Db = CurrentDb()
For I = 0 To Db.TableDefs.Count - 1
Set tdf = Db.TableDefs(I)
If tdf.Connect <> "" Then
j = InStr(1, UCase(tdf.Connect), "DATABASE=")
k = InStr(1, UCase(tdf.Connect), "TABLE=")
If Left(tdf.NAME, 1) <> "~" Then
strConnect = Left(tdf.Connect, j + 8) & txtDataMDB & ";Table=" & tdf.NAME
tdf.Connect = strConnect
tdf.RefreshLink
'DATABASE=\\YSNHDQFS01\Personal$\Kenneth.Reay\My Documents\Ken\BExxyy.mdb;TABLE=tblToLink
End If
End If
Next I
Set Db = Nothing
End If
DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NorthNone]

Thanks and a star!

---------------------------------------
The customer may not always be right, but the customer is always the customer.