Splitting database

[Amy Blankenship]

Hello, all;

I have been working on a DB for a client. They are based in another state,
so I keep sending them the whole database to test. What I'd like to do is
split the database so that I can continue to work on the FE and the queries,
and they can get on with entering data in the tables, because the production
date is getting closer & closer. But before I do this, I want to be really
sure it will work. So I have some questions.

First, when I split the DB, which side will the queries land on?
Second, can I give the FE a relative path to the backend? (i.e.
".\backend.mdb") If so, where does this go?
Third, will it still be possible to run queries from outside the database?
"Exec [myquery] param1, param2"
If the queries wind up in the FE, will it be possible to move them back to
the backend in the production DB? If so, how would I go about that?

The access help seems to be a bit thin on this sort of info. Or maybe I'm a
bit thick...

Thanks;

Amy

 

[Klatuu]

See answers below:

Hello, all;

I have been working on a DB for a client. They are based in another state,
so I keep sending them the whole database to test. What I'd like to do is
split the database so that I can continue to work on the FE and the queries,
and they can get on with entering data in the tables, because the production
date is getting closer & closer. But before I do this, I want to be really
sure it will work. So I have some questions.

First, when I split the DB, which side will the queries land on?

Queries will be in the FE. Only tables will reside in the BE. All other
objects are in the FE.

Second, can I give the FE a relative path to the backend? (i.e.
".\backend.mdb") If so, where does this go?

No, you will need to use either a drive path, or preferably a UNC path
(server name and path). This is preferred because different users may have
different drive mappings, so if User A has a server folder mapped as S: and
User B has it mapped as F:, then if the links are specified as
S:\SomeFolder\MyDatabase.mdb, the User B will get an error.

Third, will it still be possible to run queries from outside the database?

When you say "outside the database", I assume you mean outside the BE. The
answer is yes.

"Exec [myquery] param1, param2"
If the queries wind up in the FE, will it be possible to move them back to
the backend in the production DB? If so, how would I go about that?

They don't go there. All objects except tables go in the FE. From a
practical point of view, you would not know whether the table you are
referencing is a local table or a linked table.

The access help seems to be a bit thin on this sort of info. Or maybe I'm a
bit thick...

It take a little getting used to. The trickiest part is managing the links.
While you are doing your development, you will have the links establised to
the location of your BE. Once you deliver that to the client, those links
are no longer valid. You will have to have a method to relink to the BE for
each user. We will get back to that in a moment.
What I want to point out now is that the FE should NEVER be on a shared
folder or used by more than one user. It works, but there are a number of
issues - more than I have time to discuss at the moment. Just trust me on
this one. Each user should have his own copy of the FE on his computer.
(Fewer problems, runs faster, reduces network traffic).
Now the relink issue. The first time a user opens the FE and tries to access
data in the BE after you deliver it, it will error because it can't find the
tables. What you need to do in whatever code runs when you start up is to
test to see if the links are valid and If they are not, progammatically
relink. This gets a bit involved, because you will need to test each table's
connection property. Here is some sample code that shows how to test for a
good connection:

On Error Resume Next

Set dbs = CurrentDb()
For intCount = dbs.TableDefs.Count - 1 To 0 Step -1 ' Look for a linked
table
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then ' This is not a linked table
strConnect = dbs.TableDefs(dbs.TableDefs(0).NAME).Connect '
Find The Current Link
tdf.Connect = strConnect
tdf.RefreshLink ' Try to connect to it
If Err = 0 Then ' The link is still good, carry on
Exit For
Else
********
Then, I would suggest using a common dialog box to allow the user to select
the BE (This will require a little user training)

Now, as to relinking:
Here is a function that performs the relink (strNewPath is the full path and
name of the BE database:

Private Function ReLink(strNewPath As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Dim intCount As Integer
Dim frmCurrentForm As Form

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function
*************
Obviously, you will need to adapt this to your application. Post back if
you have questions.

 

[Amy Blankenship]

OK, what you're saying is that

(1) Yes, I can have a relative path (which is really, actually, what I want
and need), by using Codeproject.Path instead of strNewPath
(2) If there's a way to move the queries from the FE to the BE (which is
really, actually, what I want and need), you either don't know or, because
you've decided I don't really want and need to do that, you're not telling
me.

Is there an interface way or a quicker way to implement (1)? I really,
actually JUST want to be able to hand them a separate FE that can sit in the
same directory as the BE tables and be used by just one user on that
computer. This is different from the usual reasoning behind splitting, I
know, but it really IS what I need.

Thanks;

Amy

See answers below:

Hello, all;

I have been working on a DB for a client. They are based in another
state,
so I keep sending them the whole database to test. What I'd like to do
is
split the database so that I can continue to work on the FE and the
queries,
and they can get on with entering data in the tables, because the
production
date is getting closer & closer. But before I do this, I want to be
really
sure it will work. So I have some questions.

First, when I split the DB, which side will the queries land on?

Queries will be in the FE. Only tables will reside in the BE. All other
objects are in the FE.

Second, can I give the FE a relative path to the backend? (i.e.
".\backend.mdb") If so, where does this go?

No, you will need to use either a drive path, or preferably a UNC path
(server name and path). This is preferred because different users may
have
different drive mappings, so if User A has a server folder mapped as S:
and
User B has it mapped as F:, then if the links are specified as
S:\SomeFolder\MyDatabase.mdb, the User B will get an error.

Third, will it still be possible to run queries from outside the
database?

When you say "outside the database", I assume you mean outside the BE.
The
answer is yes.

"Exec [myquery] param1, param2"
If the queries wind up in the FE, will it be possible to move them back
to
the backend in the production DB? If so, how would I go about that?

They don't go there. All objects except tables go in the FE. From a
practical point of view, you would not know whether the table you are
referencing is a local table or a linked table.

The access help seems to be a bit thin on this sort of info. Or maybe
I'm a
bit thick...

It take a little getting used to. The trickiest part is managing the
links.
While you are doing your development, you will have the links establised
to
the location of your BE. Once you deliver that to the client, those links
are no longer valid. You will have to have a method to relink to the BE
for
each user. We will get back to that in a moment.
What I want to point out now is that the FE should NEVER be on a shared
folder or used by more than one user. It works, but there are a number of
issues - more than I have time to discuss at the moment. Just trust me on
this one. Each user should have his own copy of the FE on his computer.
(Fewer problems, runs faster, reduces network traffic).
Now the relink issue. The first time a user opens the FE and tries to
access
data in the BE after you deliver it, it will error because it can't find
the
tables. What you need to do in whatever code runs when you start up is to
test to see if the links are valid and If they are not, progammatically
relink. This gets a bit involved, because you will need to test each
table's
connection property. Here is some sample code that shows how to test for a
good connection:

On Error Resume Next

Set dbs = CurrentDb()
For intCount = dbs.TableDefs.Count - 1 To 0 Step -1 ' Look for a linked
table
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then ' This is not a linked table
strConnect = dbs.TableDefs(dbs.TableDefs(0).NAME).Connect '
Find The Current Link
tdf.Connect = strConnect
tdf.RefreshLink ' Try to connect to it
If Err = 0 Then ' The link is still good, carry on
Exit For
Else
********
Then, I would suggest using a common dialog box to allow the user to
select
the BE (This will require a little user training)

Now, as to relinking:
Here is a function that performs the relink (strNewPath is the full path
and
name of the BE database:

Private Function ReLink(strNewPath As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Dim intCount As Integer
Dim frmCurrentForm As Form

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table
'"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" &
"
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function
*************
Obviously, you will need to adapt this to your application. Post back if
you have questions.

Thanks;

Amy

 

[Klatuu]

(1) Without testing it, I think it will work. Since it will be used by only
one user, this is fine. Splitting, in this case, allows you to deliver a new
version of the FE without disturbing the data. In fact, I will remember
this, because it is a pretty good idea.

(2) Yes, you can have queries in the BE. You will want to open the BE and
import them from the FE. The question is why? There is no reason I can
think of to do that. I don't believe you really need to, I think you just
want to. I would, however, like to know your reasoning for doing that.
If fact, when you use the database spiltter, it puts all tables in the BE
and leaves everything else in the FE. Do you get the clue here? This is
really the correct way to do it.

Now, I will admit there can be valid reasons to violate a good sense rule
and I really would like to know why you think it is necessary.

OK, what you're saying is that

(1) Yes, I can have a relative path (which is really, actually, what I want
and need), by using Codeproject.Path instead of strNewPath
(2) If there's a way to move the queries from the FE to the BE (which is
really, actually, what I want and need), you either don't know or, because
you've decided I don't really want and need to do that, you're not telling
me.

Is there an interface way or a quicker way to implement (1)? I really,
actually JUST want to be able to hand them a separate FE that can sit in the
same directory as the BE tables and be used by just one user on that
computer. This is different from the usual reasoning behind splitting, I
know, but it really IS what I need.

Thanks;

Amy

See answers below:

Hello, all;

I have been working on a DB for a client. They are based in another
state,
so I keep sending them the whole database to test. What I'd like to do
is
split the database so that I can continue to work on the FE and the
queries,
and they can get on with entering data in the tables, because the
production
date is getting closer & closer. But before I do this, I want to be
really
sure it will work. So I have some questions.

First, when I split the DB, which side will the queries land on?

Queries will be in the FE. Only tables will reside in the BE. All other
objects are in the FE.

Second, can I give the FE a relative path to the backend? (i.e.
".\backend.mdb") If so, where does this go?

No, you will need to use either a drive path, or preferably a UNC path
(server name and path). This is preferred because different users may
have
different drive mappings, so if User A has a server folder mapped as S:
and
User B has it mapped as F:, then if the links are specified as
S:\SomeFolder\MyDatabase.mdb, the User B will get an error.

Third, will it still be possible to run queries from outside the
database?

When you say "outside the database", I assume you mean outside the BE.
The
answer is yes.

"Exec [myquery] param1, param2"
If the queries wind up in the FE, will it be possible to move them back
to
the backend in the production DB? If so, how would I go about that?

They don't go there. All objects except tables go in the FE. From a
practical point of view, you would not know whether the table you are
referencing is a local table or a linked table.

The access help seems to be a bit thin on this sort of info. Or maybe
I'm a
bit thick...

It take a little getting used to. The trickiest part is managing the
links.
While you are doing your development, you will have the links establised
to
the location of your BE. Once you deliver that to the client, those links
are no longer valid. You will have to have a method to relink to the BE
for
each user. We will get back to that in a moment.
What I want to point out now is that the FE should NEVER be on a shared
folder or used by more than one user. It works, but there are a number of
issues - more than I have time to discuss at the moment. Just trust me on
this one. Each user should have his own copy of the FE on his computer.
(Fewer problems, runs faster, reduces network traffic).
Now the relink issue. The first time a user opens the FE and tries to
access
data in the BE after you deliver it, it will error because it can't find
the
tables. What you need to do in whatever code runs when you start up is to
test to see if the links are valid and If they are not, progammatically
relink. This gets a bit involved, because you will need to test each
table's
connection property. Here is some sample code that shows how to test for a
good connection:

On Error Resume Next

Set dbs = CurrentDb()
For intCount = dbs.TableDefs.Count - 1 To 0 Step -1 ' Look for a linked
table
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then ' This is not a linked table
strConnect = dbs.TableDefs(dbs.TableDefs(0).NAME).Connect '
Find The Current Link
tdf.Connect = strConnect
tdf.RefreshLink ' Try to connect to it
If Err = 0 Then ' The link is still good, carry on
Exit For
Else
********
Then, I would suggest using a common dialog box to allow the user to
select
the BE (This will require a little user training)

Now, as to relinking:
Here is a function that performs the relink (strNewPath is the full path
and
name of the BE database:

Private Function ReLink(strNewPath As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Dim intCount As Integer
Dim frmCurrentForm As Form

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table
'"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" &
"
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function
*************
Obviously, you will need to adapt this to your application. Post back if
you have questions.

Thanks;

Amy

 

[Amy Blankenship]

(1) Without testing it, I think it will work. Since it will be used by
only
one user, this is fine. Splitting, in this case, allows you to deliver a
new
version of the FE without disturbing the data. In fact, I will remember
this, because it is a pretty good idea.

(2) Yes, you can have queries in the BE. You will want to open the BE and
import them from the FE. The question is why? There is no reason I can
think of to do that. I don't believe you really need to, I think you just
want to. I would, however, like to know your reasoning for doing that.
If fact, when you use the database spiltter, it puts all tables in the BE
and leaves everything else in the FE. Do you get the clue here? This is
really the correct way to do it.

Because I have a robust and time-proven method for calling stored procedures
either from asp pages or from a local database from within my primary
development platform, Macromedia Authorware. If the stored queries are in
the FE file, then BOTH the FE and the BE have to be installed locally or
uploaded to the web, increasing the storage space needed for no practical
gain. So a secondary goal of this whole splitting thing is to strip out the
things that won't be needed in the final course (this is an eLearning
project), like forms and reports, while keeping the data and the queries the
system expects to use to access the data. By splitting the DB, I retain the
advantages of an Access FE, even after we deploy, without the overhead
involved with a lot of forms and reports.

If I'm understanding you correctly, once the queries are finalized, they
just look at their own local table (linked or not) and won't care that the
data in the tables is linked, so it can just be imported wholesale into the
BE. And if we need to add more data in after the fact, the FE won't care
that the BE also has a copy of that same query.

Yes?

Thanks;

Amy

 

[Klatuu]

You are correct.

Because I have a robust and time-proven method for calling stored procedures
either from asp pages or from a local database from within my primary
development platform, Macromedia Authorware. If the stored queries are in
the FE file, then BOTH the FE and the BE have to be installed locally or
uploaded to the web, increasing the storage space needed for no practical
gain. So a secondary goal of this whole splitting thing is to strip out the
things that won't be needed in the final course (this is an eLearning
project), like forms and reports, while keeping the data and the queries the
system expects to use to access the data. By splitting the DB, I retain the
advantages of an Access FE, even after we deploy, without the overhead
involved with a lot of forms and reports.

If I'm understanding you correctly, once the queries are finalized, they
just look at their own local table (linked or not) and won't care that the
data in the tables is linked, so it can just be imported wholesale into the
BE. And if we need to add more data in after the fact, the FE won't care
that the BE also has a copy of that same query.

Yes?

Thanks;

Amy

 

[Amy Blankenship]

One more question:

In your original reply, you posted this code:

On Error Resume Next

Set dbs = CurrentDb()
For intCount = dbs.TableDefs.Count - 1 To 0 Step -1 ' Look for a linked
table
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then ' This is not a linked table
strConnect = dbs.TableDefs(dbs.TableDefs(0).NAME).Connect '
Find The Current Link
tdf.Connect = strConnect
tdf.RefreshLink ' Try to connect to it
If Err = 0 Then ' The link is still good, carry on
Exit For
Else

Why are we trying to connect to a table that's not linked? Or is that a
typo?

Thanks;

Amy

 

[Klatuu]

This code is testing to see if the link is valid. Notice the On Error Resume
Next. If you attempt to refresh the link and the connection path is not
valid, it creates an error. Based on the error, we know we need to relink.

 

[Amy Blankenship]

But according to your comment, we are only doing this on tables that are NOT
linked (If tdf.Connect <> "" Then ' This is not a linked table)


If the table is NOT linked, then would it even be possible to relink?

Or is it just a typo in the comment (extra not)

Thanks;

Amy

 

[Klatuu]

You are reading the code backwards. What is is saying is that if connect is
NOT a zero length string, then try the connect. If connect is not a zero
length string then it is a linked table. If it is a zero length string, it
is a local table, so it does not attempt a connect.

 

[Amy Blankenship]

I was reading the code just fine. It was your comment that confused me. It
conflicted with the code, and since the code appears to more be a code
snippet or pseudocode, I wanted to be clear on what is happening before I
cause myself a lot of grief by assuming the comment is correct (and I should
change the code to match) or the code is correct (and I should ignore the
comment).

This is like talking to my husband. You're not Scottish, are you? ;-)

-Amy

 

[Klatuu]

It appears the comment is contridictory to the code; however, the code is
correct. It is not psuedo code, it has been running in production for over
five years. I will change the comment to correctly reflect what is happening.

 

[Amy Blankenship]

It appears the comment is contridictory to the code; however, the code is
correct. It is not psuedo code, it has been running in production for
over
five years. I will change the comment to correctly reflect what is
happening.

I did say pseudo code or a fragment...since it cuts off after the else
abruptly, which I'm wondering if Access would even let you do.

I actually wound up hacking the code from Dev Ashish's site, plus some stuff
on another site I found, plus a few lines from yours.

Thanks!

-Amy

 

[Klatuu]

Great. Yes, the code was a fragment. It was offered only as an example.
Much of the code I did not send was specific to the application.
I hope you accomplish what you are trying to do.

I noticed you had posted a question regarding the descriptions for linked
tables. It is normal for the descriptions not to be on the FE. I don't know
why, but I suspect it is so the description on the FE can be entered to suit
that FE. You can enter a description on a linked table. A pain to have to
do, but that is the way it is.