Sharing issues

[Guest]

Hi, there.
All was well until we put the db on a server and now have multiple users.

I have code that checks the Member's last name to see if there is a
duplicate entry. Now that many users are entering Members, the code seems
confused as to what last name it is checking.

Any thoughts on how to address this sharing issue? Thanks- having the db on
a server is new to us. We haven't split the db yet.

 

[Guest]

Hi Stephanie,

Without seeing your code, it is difficult to comment too much. However, you
really *should* split the database.

Split the database
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB
http://home.bendbroadband.com/conradsystems/accessjunkie/splitting.html

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Thanks for the links- I appreciate you taking the time to reply. We really
are planning to split the db soon.

I've included the code in case something catches your eye...

In debug, the code craps out:
If Not IsNull(Me.LastName) And (DonorTypeID = "CU" Or
DonorTypeID = "IN"
Me.Sndx = Soundex(Me.LastName)

(which makes sense if it is confused as to which LastName it is trying to
verify when more than one person is entering a LastName).

Here's the code:

'NEW SOUNDEX CODE FROM STEVES
'is this a new entry?
If (Me.NewRecord = True) Then

'DONOR TYPES
' CU - Customer, IN - Individual
' BU - Business, CI - ???
' FO - ??? , ME - ???

Select Case Me.DonorTypeID
Case "BU", "CI", "FO", "ME"

Case "CU", "IN"
'if more cases to run the soundex code are added, don't forget to
change the strSQL as noted.
Dim varID As Variant
Dim rst As DAO.Recordset, strNames As String
Dim gstrAppTitle As String
Dim strSQL As String
Dim SName As String

gstrAppTitle = "Name Check"

If IsNull(Me.LastName) Then
'for CU & IN donor types
MsgBox "For Donor Type 'Customer' and 'Individual', the Last
Name is Required, vbExclamation + vbOKOnly"
Cancel = True
Else ' Check for similar name

' ******** LastName is the name of the control on the form
' that is bound to the LastName field
SName = Soundex(Me.LastName)
' 'for debugging
' Debug.Print SName

' Open a recordset to look for similar names
strSQL = "SELECT LastName, FirstName, Sndx"
strSQL = strSQL & " FROM contacts"
strSQL = strSQL & " Where (DonorTypeID = 'CU' OR DonorTypeID
= 'IN')"
'if more DonorTypeID are added in which we want to run
soundex, don't forget to add them here as well.
strSQL = strSQL & " AND Sndx = '" & SName & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName;"

' 'for debugging
' Debug.Print strSQL


Set rst = CurrentDb.OpenRecordset(strSQL)

'check for records in recordset
If Not (rst.BOF And rst.EOF) Then
rst.MoveLast
rst.MoveFirst
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " &
rst!FirstName & vbCrLf
rst.MoveNext
Loop
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox(" There are members with similar "
& "last names already saved in the database: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure
this member is not a duplicate?", vbQuestion + vbYesNo + vbDefaultButton2,
gstrAppTitle) Then

' Cancel the save
Cancel = True
' Me.Undo
Me.LastName.SetFocus

Else
' good name - add soundex code and save record
Me.Sndx = SName
End If 'If vbNo
End If 'If Len(strNames) > 0
End If 'If rst.RecordCount > 0
' Done with the recordset
rst.Close
Set rst = Nothing
End If 'If Not IsNull(Me.LastName)
End Select

Else 'not a new entry
' saves soundex code when editing current record

' ******** LName is the name of the control on the form
' that is bound to the LastName field
If Not IsNull(Me.LastName) And (DonorTypeID = "CU" Or DonorTypeID =
"IN") Then
Me.Sndx = Soundex(Me.LastName)
End If
End If 'If (Me.NewRecord = True)

End Sub

 

[Guest]

Try splitting your application. Install a copy of the front-end on each
user's local hard drive. Then see if you still have an issue.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[5th Amendment]

CORRECTION!

splitting is no longer necessary; it is reccomended to move to Access Data
Projects for all Microsoft Access applications

 

[Guest]

OK, I've practiced splitting the db ;-) thanks for the push to do so.

So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!

I'm planning to use Tony's free FE updater, that updates the new FE to all
users on the network when we place a new version in the directory where the
BE is.

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on both the FE and BE?

Anything else I need to do?

Thanks for the advice!

 

[Guest]

Hi Stephanie,

;-) thanks for the push to do so.

Then I have the "original name_be" portion of the database that only has
tables.

Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:

Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc.)

Much Better
\\Server\share\Folder1\

It was easy, so I imagine the hard part is coming up!

Splitting is generally pretty easy, but you should, as a minimum, do three
things:

1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.

These ideas, and a lot more, are discussed in this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I'm planning to use Tony's free FE updater, ...

This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/autofe.zip

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater.

The FE that you house on the file server should not be a shared copy. It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.

I'll create a shortcut to the FE on each users desktop.

Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.

Periodically, we'll need to compact and repair- is that on both the FE and BE?

I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy of the FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.

Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:

http://tinyurl.com/2dmpw

You should definately compact the BE database on a periodic basis, as well
as make (and test) backup copies of the BE database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Thanks for all of the tips!

I was able to follow your first 2 steps:
1.) Ensure that Name Autocorrect is disabled
2.) Set all table SubDatasheets to [None]
and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

But I'm a little less clear on
3.) Establish a persistant connection to the BE database
your fabulous article states:
An easy method of doing this is to create a table in the back end database
that has just one record. For example, this record might indicate the latest
version number of the front end database. Create a form that is bound to
this table.

I did create a table in the BE:
tblFEVersion
FEVersionID (autonumber)
FEVersion (text)

And created a form that is bound to the table.
So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode when the
front end database is opened.
Do you have sample code for opening the form in hidden mode that you would
share?

As for location on the server:
I'll create 2 folders (UNC named!):
1) BE and StartMDB.exe
2) FE

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
..Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

I appreciate all your time! And the expertise you share with the discussion
group!
Cheers,
Stephanie

Hi Stephanie,

;-) thanks for the push to do so.

Then I have the "original name_be" portion of the database that only has
tables.

Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:

Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc.)

Much Better
\\Server\share\Folder1\

It was easy, so I imagine the hard part is coming up!

Splitting is generally pretty easy, but you should, as a minimum, do three
things:

1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.

These ideas, and a lot more, are discussed in this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I'm planning to use Tony's free FE updater, ...

This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/autofe.zip

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater.

The FE that you house on the file server should not be a shared copy. It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.

I'll create a shortcut to the FE on each users desktop.

Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.

Periodically, we'll need to compact and repair- is that on both the FE and BE?

I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy of the FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.

Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:

http://tinyurl.com/2dmpw

You should definately compact the BE database on a periodic basis, as well
as make (and test) backup copies of the BE database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

OK, I've practiced splitting the db ;-) thanks for the push to do so.

So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!

I'm planning to use Tony's free FE updater, that updates the new FE to all
users on the network when we place a new version in the directory where the
BE is.

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on both the FE and BE?

Anything else I need to do?

Thanks for the advice!

 

[Susie Johnson]

what a crock of crap Tom

move to Acceess Data Projects and STFU

Hi Stephanie,

;-) thanks for the push to do so.

Then I have the "original name_be" portion of the database that only has
tables.

Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:

Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc.)

Much Better
\\Server\share\Folder1\

It was easy, so I imagine the hard part is coming up!

Splitting is generally pretty easy, but you should, as a minimum, do three
things:

1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.

These ideas, and a lot more, are discussed in this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I'm planning to use Tony's free FE updater, ...

This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/autofe.zip

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater.

The FE that you house on the file server should not be a shared copy. It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.

I'll create a shortcut to the FE on each users desktop.

Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.

Periodically, we'll need to compact and repair- is that on both the FE

and BE?

I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy of the FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.

Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:

http://tinyurl.com/2dmpw

You should definately compact the BE database on a periodic basis, as well
as make (and test) backup copies of the BE database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

OK, I've practiced splitting the db ;-) thanks for the push to do so.

So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!

I'm planning to use Tony's free FE updater, that updates the new FE to all
users on the network when we place a new version in the directory where the
BE is.

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on both the FE and BE?

Anything else I need to do?

Thanks for the advice!

 

[Guest]

Hi Stephanie,

and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

Thank You for the nice compliment. Did you try downloading the Word document
that I provided the link to, for setting up Tony Toews' AutoFE Updater tool?

So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

You can do this, or you can simply have a one field table, say:

Table name: tblThoughtOfTheDay
Field name: ThoughtOfTheDay (Text/255 Primary key)

Add the following record: "I love pizza!"

Then, the form that you open in hidden mode would have tblThoughtOfTheDay as
it's recordsource, with a text box bound to the ThoughtOfTheDay field.

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode when the
front end database is opened. Do you have sample code for opening the form in
hidden mode that you would share?

If you want to use VBA code, add the following line of code to whatever form
that you are opening at startup (likely a Switchboard form of some type):

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

DoCmd.OpenForm "frmThoughtOfTheDay", WindowMode:=acHidden

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open event procedure..."
Resume ExitProc
End Sub


However, I recommend using an Autoexec macro instead. Create a new macro and
name it "Autoexec" (not case sensitive). Add the following Action:

OpenForm
Add an appropriate comment, such as "Open Switchboard form". Press the F6
button to toggle to the lower window. Enter the name of your switchboard
form. Leave the View setting at the default "Form", and WindowMode at the
default "Normal". Press F6 once more to toggle back to the top.

Add another OpenForm Action, with a comment such as "Open
frmThoughtOfTheDay" in hidden mode, to establish a persistent connection". In
the lower window, select the name of this form. Set the WindowMode property
to Hidden. Save the macro. Then try running it. As soon as it runs you should
see a locking database file (*.ldb) created for the BE database, in the same
folder that the BE database is saved to.

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

Therapy animals? Sounds like an organization that we have in the Puget Sound
(Seattle, WA.) area, known as Little Bit Therapeutic Riding Center
(http://www.littlebit.org/). My guess is that you would want to store the
pictures in a shared folder on the file server, so that they would be
available to all users.

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

Comments, and commented out code, contribute a negligible amount to bloat.
If you distribute in the compiled .mde form--which I highly recommend--then
all canocial text (the VBA code + comments that you can see) is removed
anyway, so in that case, the negligible contribution becomes zero.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks for all of the tips!

I was able to follow your first 2 steps:
1.) Ensure that Name Autocorrect is disabled
2.) Set all table SubDatasheets to [None]
and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

But I'm a little less clear on
3.) Establish a persistant connection to the BE database
your fabulous article states:
An easy method of doing this is to create a table in the back end database
that has just one record. For example, this record might indicate the latest
version number of the front end database. Create a form that is bound to
this table.

I did create a table in the BE:
tblFEVersion
FEVersionID (autonumber)
FEVersion (text)

And created a form that is bound to the table.
So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode when the
front end database is opened.
Do you have sample code for opening the form in hidden mode that you would
share?

As for location on the server:
I'll create 2 folders (UNC named!):
1) BE and StartMDB.exe
2) FE

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

I appreciate all your time! And the expertise you share with the discussion
group!
Cheers,
Stephanie

Hi Stephanie,

;-) thanks for the push to do so.

Then I have the "original name_be" portion of the database that only has
tables.

Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:

Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc.)

Much Better
\\Server\share\Folder1\

It was easy, so I imagine the hard part is coming up!

Splitting is generally pretty easy, but you should, as a minimum, do three
things:

1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.

These ideas, and a lot more, are discussed in this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I'm planning to use Tony's free FE updater, ...

This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/autofe.zip

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater.

The FE that you house on the file server should not be a shared copy. It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.

I'll create a shortcut to the FE on each users desktop.

Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.

Periodically, we'll need to compact and repair- is that on both the FE and BE?

I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy of the FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.

Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:

http://tinyurl.com/2dmpw

You should definately compact the BE database on a periodic basis, as well
as make (and test) backup copies of the BE database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

OK, I've practiced splitting the db ;-) thanks for the push to do so.

So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!

I'm planning to use Tony's free FE updater, that updates the new FE to all
users on the network when we place a new version in the directory where the
BE is.

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on both the FE and BE?

Anything else I need to do?

Thanks for the advice!

 

[IRS Intern]

Jeez, Tom-- you worked for Little Bit?

I used to live about 2 blocks from their stable on Mink Road; many
many moons ago

My mom made me go and shovel horse crap once

Hi Stephanie,

and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

Thank You for the nice compliment. Did you try downloading the Word document
that I provided the link to, for setting up Tony Toews' AutoFE Updater tool?

So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

You can do this, or you can simply have a one field table, say:

Table name: tblThoughtOfTheDay
Field name: ThoughtOfTheDay (Text/255 Primary key)

Add the following record: "I love pizza!"

Then, the form that you open in hidden mode would have tblThoughtOfTheDayas
it's recordsource, with a text box bound to the ThoughtOfTheDay field.

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode whenthe
front end database is opened. Do you have sample code for opening the form in
hidden mode that you would share?

If you want to use VBA code, add the following line of code to whatever form
that you are opening at startup (likely a Switchboard form of some type):

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

DoCmd.OpenForm "frmThoughtOfTheDay", WindowMode:=acHidden

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open event procedure..."
Resume ExitProc
End Sub

However, I recommend using an Autoexec macro instead. Create a new macro and
name it "Autoexec" (not case sensitive). Add the following Action:

OpenForm
Add an appropriate comment, such as "Open Switchboard form". Press the F6
button to toggle to the lower window. Enter the name of your switchboard
form. Leave the View setting at the default "Form", and WindowMode at the
default "Normal". Press F6 once more to toggle back to the top.

Add another OpenForm Action, with a comment such as "Open
frmThoughtOfTheDay" in hidden mode, to establish a persistent connection".. In
the lower window, select the name of this form. Set the WindowMode property
to Hidden. Save the macro. Then try running it. As soon as it runs you should
see a locking database file (*.ldb) created for the BE database, in the same
folder that the BE database is saved to.

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

Therapy animals? Sounds like an organization that we have in the Puget Sound
(Seattle, WA.) area, known as Little Bit Therapeutic Riding Center
(http://www.littlebit.org/). My guess is that you would want to store the
pictures in a shared folder on the file server, so that they would be
available to all users.

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

Comments, and commented out code, contribute a negligible amount to bloat.
If you distribute in the compiled .mde form--which I highly recommend--then
all canocial text (the VBA code + comments that you can see) is removed
anyway, so in that case, the negligible contribution becomes zero.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks for all of the tips!

I was able to follow your first 2 steps:
1.) Ensure that Name Autocorrect is disabled
2.) Set all table SubDatasheets to [None]
and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

But I'm a little less clear on
3.) Establish a persistant connection to the BE database
your fabulous article states:
An easy method of doing this is to create a table in the back end database
that has just one record. For example, this record might indicate the latest
version number of the front end database. Create a form that is bound to
this table.

I did create a table in the BE:
tblFEVersion
FEVersionID (autonumber)
FEVersion (text)

And created a form that is bound to the table.
So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode whenthe
front end database is opened.
Do you have sample code for opening the form in hidden mode that you would
share?

As for location on the server:
I'll create 2 folders (UNC named!):
1) BE and StartMDB.exe
2) FE

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

I appreciate all your time! And the expertise you share with the discussion
group!
Cheers,
Stephanie

Hi Stephanie,
;-) thanks for the push to do so.
Anytime! <smile>
Then I have the "original name_be" portion of the database that only has
tables.
Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:
Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc..)
Much Better
\\Server\share\Folder1\
It was easy, so I imagine the hard part is coming up!
Splitting is generally pretty easy, but you should, as a minimum, do three
things:
1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.
These ideas, and a lot more, are discussed in this article:
Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html
I'm planning to use Tony's free FE updater, ...
This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copyhere:
http://home.comcast.net/~tutorme2/samples/autofe.zip
I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and theFE
updater.
The FE that you house on the file server should not be a shared copy.It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.
I'll create a shortcut to the FE on each users desktop.
Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.
Periodically, we'll need to compact and repair- is that on both theFE and BE?
I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy ofthe FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.
Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:
http://tinyurl.com/2dmpw
You should definately compact the BE database on a periodic basis, aswell
as make (and test) backup copies of the BE database.
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
OK, I've practiced splitting the db ;-) thanks for the push to do so.
So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!
I'm planning to use Tony's free FE updater, that updates the new FEto all
users on the network when we place a new version in the directory where the
BE is.
I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and theFE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on

...

read more »- Hide quoted text -

- Show quoted text -

 

[Guest]

Tom,
I am so sorry- I did not see your reply until just now. Thanks for taking
the time!

I did print out your document about Tony's FE loader. Thanks for that! I'm
trying to collect enough information to make sure I split the db correctly
the first time!

Thanks for the link to "Little Bit"- it sounds like a fabulous program. I
volunteer with my therapy dogs at hospitals, residential facilities for
abused kids, detention centers and libraries. The dogs love it and the kids
love visiting with the dogs! I appreciate all of the fabulous folks from the
discussion group who have helped get our non-profit's homemade db up and
running! You can check us out at www.therapyanimals.org

Persistent connection: a snap, with your step-by-step instructions! Locking
and all- Thanks!

Two things (at least!) that I'm missing, if you have time.

1) You have mentioned "If you distribute in the compiled .mde form"... so, I
split the db using the db splitter tool. Was there something else I needed to
do to get it to the complied .mde form?

2) This is probably silly, but once the FE and BE are out on the server in
their respective folders, I understand I can "point" FE to different BEs and
vice versa. So I can have a test FE out there and when it's ready to be
"production" I can point the BE at it or it at the BE perhaps? How do I do
"point"?

I'll pay closer attention to replies (and not trust "notify me of replies"
to work!).

I appreciate your help. We almost tried to split the db this weekend, but I
needed just a bit more information! Thanks!
Cheers,
Stephanie

Hi Stephanie,

and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

Thank You for the nice compliment. Did you try downloading the Word document
that I provided the link to, for setting up Tony Toews' AutoFE Updater tool?

So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

You can do this, or you can simply have a one field table, say:

Table name: tblThoughtOfTheDay
Field name: ThoughtOfTheDay (Text/255 Primary key)

Add the following record: "I love pizza!"

Then, the form that you open in hidden mode would have tblThoughtOfTheDay as
it's recordsource, with a text box bound to the ThoughtOfTheDay field.

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode when the
front end database is opened. Do you have sample code for opening the form in
hidden mode that you would share?

If you want to use VBA code, add the following line of code to whatever form
that you are opening at startup (likely a Switchboard form of some type):

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

DoCmd.OpenForm "frmThoughtOfTheDay", WindowMode:=acHidden

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Open event procedure..."
Resume ExitProc
End Sub


However, I recommend using an Autoexec macro instead. Create a new macro and
name it "Autoexec" (not case sensitive). Add the following Action:

OpenForm
Add an appropriate comment, such as "Open Switchboard form". Press the F6
button to toggle to the lower window. Enter the name of your switchboard
form. Leave the View setting at the default "Form", and WindowMode at the
default "Normal". Press F6 once more to toggle back to the top.

Add another OpenForm Action, with a comment such as "Open
frmThoughtOfTheDay" in hidden mode, to establish a persistent connection". In
the lower window, select the name of this form. Set the WindowMode property
to Hidden. Save the macro. Then try running it. As soon as it runs you should
see a locking database file (*.ldb) created for the BE database, in the same
folder that the BE database is saved to.

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

Therapy animals? Sounds like an organization that we have in the Puget Sound
(Seattle, WA.) area, known as Little Bit Therapeutic Riding Center
(http://www.littlebit.org/). My guess is that you would want to store the
pictures in a shared folder on the file server, so that they would be
available to all users.

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

Comments, and commented out code, contribute a negligible amount to bloat.
If you distribute in the compiled .mde form--which I highly recommend--then
all canocial text (the VBA code + comments that you can see) is removed
anyway, so in that case, the negligible contribution becomes zero.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks for all of the tips!

I was able to follow your first 2 steps:
1.) Ensure that Name Autocorrect is disabled
2.) Set all table SubDatasheets to [None]
and hurray for me! - you don't even know ;-) But your links made it very
easy- thanks!

But I'm a little less clear on
3.) Establish a persistant connection to the BE database
your fabulous article states:
An easy method of doing this is to create a table in the back end database
that has just one record. For example, this record might indicate the latest
version number of the front end database. Create a form that is bound to
this table.

I did create a table in the BE:
tblFEVersion
FEVersionID (autonumber)
FEVersion (text)

And created a form that is bound to the table.
So would I want to manually enter the FE version number into the BE form
each time there is a "new release" of the FE?

I don't know how to do this part:
Use VBA code or an Autoexec macro to open this form in hidden mode when the
front end database is opened.
Do you have sample code for opening the form in hidden mode that you would
share?

As for location on the server:
I'll create 2 folders (UNC named!):
1) BE and StartMDB.exe
2) FE

Eventually, we'll have pictures of the therapy animals in the forms. Where
should I store the picture file?

As for severe bloat... I'm terrible at coding. The fabulous discussion group
has been a lifeline and I see the code I sampled trying to answer the bloat
question has
.Close
or
rst.Close
So I'm thinking that's not going to contribute to bloat...
What about code that I've commented out?

I appreciate all your time! And the expertise you share with the discussion
group!
Cheers,
Stephanie

Hi Stephanie,

;-) thanks for the push to do so.

Anytime! <smile>

Then I have the "original name_be" portion of the database that only has
tables.

Try to make sure that the "original name_be" does not exceed 8 characters.
You will get better performance in certain situations if you avoid names that
do not conform to the old DOS standard (8+3). In addition, try to place your
shared BE file close to the root level of your file server. If the system has
to traverse several folders, it will be slower. I advise using UNC (Uniform
Naming Convention) paths instead of the default for the database splitter,
which is to use paths with mapped drive letters. For example:

Not Good
\\Server\share\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\ (etc.)

Much Better
\\Server\share\Folder1\


It was easy, so I imagine the hard part is coming up!

Splitting is generally pretty easy, but you should, as a minimum, do three
things:

1.) Ensure that Name Autocorrect is disabled.
2.) Set all table SubDatasheets to [None].
3.) Establish a persistant connection to the BE database.

These ideas, and a lot more, are discussed in this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


I'm planning to use Tony's free FE updater, ...

This is an excellent utility. I'm using it myself, at The Boeing Company,
which is where I work. A few months ago I prepared a short Word document on
Tony's utility, which you might find helpful. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/autofe.zip

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater.

The FE that you house on the file server should not be a shared copy. It
represents the copy that each user will automatically copy, either the first
time or anytime you post an update. This copy of the FE should go in a folder
all by itself, because *all* files in this folder get copied to the user's
hard drive when an update is initiated. You likely don't want them copying
the BE, or the StartMDB.exe file to their local hard drive.

I'll create a shortcut to the FE on each users desktop.

Actually, let the StartMDB.exe utility create this shortcut for you. It will
include the correct target path. Then, you distribute a copy of this shortcut.

Periodically, we'll need to compact and repair- is that on both the FE and BE?

I usually don't worry about compacting the FE for each user. With Tony's
AutoFE updater, all you need to do is periodically post a new copy of the FE
(which, of course will be compacted, and preferably in the compiled .mde
format) in order to "freshen" each user's copy of the FE application.

Are you doing anything in your FE app. that might lead to severe bloat? This
includes things like local temp tables, or not properly closing and
destroying DAO recordset objects, etc. Here is a handy list of KB articles
that discusses causes of bloat:

http://tinyurl.com/2dmpw

You should definately compact the BE database on a periodic basis, as well
as make (and test) backup copies of the BE database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

OK, I've practiced splitting the db ;-) thanks for the push to do so.

So now I have the db with the "orginal name" that has all of the forms, and
the tables now have an arrow in front of their names.
Then I have the "original name_be" portion of the database that only has
tables.
It was easy, so I imagine the hard part is coming up!

I'm planning to use Tony's free FE updater, that updates the new FE to all
users on the network when we place a new version in the directory where the
BE is.

I'd appreciate a bit of advice on the technicalities:
I'm going to create a directory which will house the FE, BE and the FE
updater. I'll create a shortcut to the FE on each users desktop.
Periodically, we'll need to compact and repair- is that on both the FE and BE?

Anything else I need to do?

Thanks for the advice!

 

[Guest]

Hi Stephanie,

Not a problem. I've also found that the "notify me of replies" feature in
the web portal is not always 100% reliable.

Since you are doing this for a non-profit organization, I will volunteer to
take a look at your database if you want me to. If interested, send a
compacted and zipped copy to me. My e-mail address is available at the bottom
of the contributors page indicated below, in my signature.

1) You have mentioned "If you distribute in the compiled .mde form"... so, I
split the db using the db splitter tool. Was there something else I needed to
do to get it to the complied .mde form?

Splitting a database is entirely different from compiling to the .mde form.
To create a .mde file from a .mdb file, you must first ensure that any VBA
code compiles without an error. All users must be either at the same version
or the next later version, in order to be able to use your .mde file. For
example, if you have any users that are using Access 2000, then you will need
to use Access 2000 to create the .mde file. If you used Access 2003 to create
a .mde file, then only users with Access 2003 or 2007 will be able to open
the file. To create the .mde file, click on:

Tools > Database Utilities > Make MDE file...

If you have problems creating the .mde file, then you can check out this page:

Cannot make .MDE
http://www.access.qbuilt.com/html/conversion.html#CannotMakeMDE

You should convert the front-end (FE) to a .mde file. Make absolutely
certain to retain a copy of the .mdb file for your own use, because you
cannot open forms, reports or modules in a .mde file. There is no benefit to
converting a back-end (BE) file to the .mde format.

2) This is probably silly, but once the FE and BE are out on the server in
their respective folders, I understand I can "point" FE to different BEs and
vice versa. So I can have a test FE out there and when it's ready to be
"production" I can point the BE at it or it at the BE perhaps? How do I do
"point"?

The FE file should be installed on each users local hard drive. Yes, you
will typically have a copy of the FE in a shared folder on the file server,
but this is so that when users click on their desktop icon, the AutoFE
Updater utility will copy the latest version to the user's local hard drive.

The easiest method would be to simply have two copies of the FE, one linked
to a test BE and the other linked to a production BE. However, if you really
want to go with the pointing method, you can use a technique that Jeff Conrad
wrote up in this article:

Creating A Customized Login Screen For A Secured Database
http://www.access.qbuilt.com/html/custom_login.html

See the first paragraph under the section sub-titled "A Custom Login Form
Can Provide Multiple Solutions", about 2/3 of the way down.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

I accidently left three key words out of this sentence:

Make absolutely certain to retain a copy of the .mdb file for your own use,
because you cannot open forms, reports or modules in a .mde file.

It should have read:

Make absolutely certain to retain a copy of the .mdb file for your own use,
because you cannot open forms, reports or modules in design view, in a .mde
file.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Guest]

Tom! That is an amazingly generous offer! Thank you. I'll take you up on it
when I am not using a Sprint card.

When I compact/repair, I get a message the it can't find field Description.
I click OK and move on. I have no idea. Will this keep me from using .mde for
the front end? Since we are a non-profit with hand-me-down computers, I'm
believe not everyone is on the same Access version. I'll have to look into
that. What are the advantages of .mde?

Thanks for the clarification of placing the FE on each person's computer and
then using the updater to update it. I'm still not understanding "pointing"
or "linking" the FE to the BE. When I practiced splitting the db, I see the
FE and the BE in the folder (I'll separate the FE and the BE into different
folders). When I am finished testing a new version of the FE, how do I "link"
the new part to the BE (or if I tested adding a table, how to I "link" the
new part of the BE to the existing FE)? I know that if I don't point/link
correctly users will be mad that they would need to reenter data. The custom
login seems too complicated for me. I'd like an easy method/checklist to make
sure I've done everything correctly.

Thanks for all of your time. I just really want to make sure I split the db
correctly and treat the users right!

Thank you again for the offer to look at the db!
Cheers,
Stephanie

 

[Guest]

Hi Stephanie,

When I compact/repair, I get a message the it can't find field Description.

This does not sound good. You database may have some corruption. Please see
the 8th bullet in this listing:

http://www.granite.ab.ca/access/corruption/symptoms.htm

Here are two good articles that explain causes of corruption:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm

And here is my standard blurb for dealing with minor corruptions:

<Begin Blurb>
Create a brand new database and immediately disable the NameAutocorrupt
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's always
best to recreate the linked tables from scratch. When importing local tables,
make sure to check the option to import relationships, menus and toolbars,
and import/export specs. If any of the local tables in the source DB are
hidden, you'll need to first unhide them. You will need to set the checked
references to match the source database, along with any startup options set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system tables
(the tables whose names start with "MSYS"). These system tables are updated
appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group at a
time, re-establishing any linked tables, setting startup options, and setting
references to match the source DB is usually a fairly quick procedure. When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.

Since we are a non-profit with hand-me-down computers, I believe not
everyone is on the same Access version. I'll have to look into that.

That's okay, but you should ensure that:
1.) All machines have the latest service pack installed for the operating
system
2.) All machines have the latest service pack installed for the version of
Access (or Office) and
3.) the JET database engine.

Use this KB (Knowledge Base) article as a guide:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

You'll need to use the lowest common denominator (version) to create the
..mde file. Note: If any users are still using Access 97, then you'll need to
create two .mde files: one in the '97 version and one in the 2000 version.

What are the advantages of .mde?

The advantages include:
1.) VBA code cannot become decompiled.
2.) A reduced possibility of corruption to the FE file
3.) Users cannot change your forms, reports or module code. (They can still
hack your tables, queries and macros).
4.) A reduced chance of a user experiencing a MISSING reference problem
(although if there is a missing reference, then it can be more difficult to
deal with if you only have the .mde file). Here are two good articles that
deal with missing references:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


I'm sure I'm likely forgetting some other benefits of distributing in .mde
format at the moment...

I'm still not understanding "pointing" or "linking" the FE to the BE.

One can use the Linked Table Manager wizard to set the correct path to the
BE database: Tools > Database Utilities > Linked Table Manager...
I suggest using UNC (Uniform Naming Convention) paths, so that you are not
dependent on a user having a given drive letter mapped correctly. There is
lots of code available on the web for automatically relinking to the BE
database. If you send me a copy of your database, I will add this
functionality to it.




Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Wow! I've got some reading to do- thanks for the links. I'll try to fix the
db as soon as possible.

Intermountain Therapy Animals had our fabulous Reading Education Assistance
Dogs conference over the weekend. 110 people from 36 states! I'm back on db
track now. I'll let you know how it goes...

 

[Guest]

Tom,
I sucessfully created a new db, imported everything by groups (no linked
tables) and complied/repaired! Sweet!
Thanks for the step-by-step (especially the reminders to check startup and
library! This has stumped me before.)

All seemed good until I got to your step about compiling code. I had a few
pieces of code that had issues. I was able to comment them out and thought I
was recompiling the code. When I navigate to Debug > Compile mydb, the
selection is now grayed-out (can't select it). Is that good or bad? So I
added a blank line to one of the codes and was once again able to see
Compile. I did so and now it's grayed-out again. I'm thinking that is good...

I have a couple of other minor changes and data privacy issues to address
and then I'll send the db on to you. Thanks for your support!
Cheers, Stephanie

 

[Guest]

Hi Stephanie,

When I navigate to Debug > Compile mydb, the
selection is now grayed-out (can't select it). Is that good or bad?

This is very good! When this option is greyed out, it means that your code
is properly compiled. Note: You can re-enable this option by making even the
slightest of changes, such as hitting the space bar one time to add a blank
space within the module, and clicking on the Save button. Not that there is
any reason to do this, but I'm just trying to make the point that small
change is enough to cause the code to need to be compiled again.

I have a couple of other minor changes and data privacy issues to address
and then I'll send the db on to you. Thanks for your support!

Okay.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Warning, Tom Wickerath is neither Most or Valuable or a Profesisonal

I would reccomend finding someone to help you with SQL Server, Access MDB is
obsolete; and it has been for a decade