A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Split db and have backend on local PC



 
 
Thread Tools Display Modes
  #1  
Old October 4th, 2009, 04:44 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy
  #2  
Old October 4th, 2009, 05:37 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default Split db and have backend on local PC

You don't mention "how" you are trying to split the db.

One approach is to make a couple copies of the unsplit db, open one and
remove everything but the data, open the other (copy) and remove only the
data (i.e., tables).

You should be able to use Windows Explorer to "manually" copy the
tables-only copy (the "back-end") to what-ever directory you want.

Then open the front-end and link to the tables in that back-end, where-ever
you put it.

Does that work?

--

Regards

Jeff Boyce
Microsoft Access MVP


"Fox" wrote in message
...
I want to split my db for other reason than to share with tons of people
and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will
install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy



  #3  
Old October 4th, 2009, 05:55 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

Hey Jeff thanks for replying...
sorry to not have given enough info...

To split the DB I'm using the wizzard...and I'm trying to put a relative
link in the name file box....however it will only exept a server location and
not a local relative path like ../My Documents/My Jewelry Data/My Jewelry
Data.accdb....

This is a project software that I am developing for crafters like myself
that want to keep track of some information in regards to costs etc...

I Package the front end and back end in two seperate packages so that the
front end can be uninstalled if I have an upgrade I need to distribute and
then they do not loose thier data.
I have a backup and restore procedure that I got working great and would be
fantastic as then I would not have to split the db's however when I add the
ms runtime to the package the backup and restore doesn't work properly....so
I'm just trying to figure out a way so that they do not loose thier data if I
have an upgrade.
When the project db install the person does not have access to any views of
the tables ect....
The way you suggest to split the db....will this allow for a relative path
and also will the person installing from the package have to link the
table....they will not have this available to them.

Thanks,
Janet
Foxy


"Jeff Boyce" wrote:

You don't mention "how" you are trying to split the db.

One approach is to make a couple copies of the unsplit db, open one and
remove everything but the data, open the other (copy) and remove only the
data (i.e., tables).

You should be able to use Windows Explorer to "manually" copy the
tables-only copy (the "back-end") to what-ever directory you want.

Then open the front-end and link to the tables in that back-end, where-ever
you put it.

Does that work?

--

Regards

Jeff Boyce
Microsoft Access MVP


"Fox" wrote in message
...
I want to split my db for other reason than to share with tons of people
and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will
install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy




  #4  
Old October 4th, 2009, 05:56 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Split db and have backend on local PC

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #5  
Old October 4th, 2009, 06:01 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #6  
Old October 4th, 2009, 06:06 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

Just to clarify , I used the wrong slashes in this post however when doing
the split I used the correct slashes so the slashes are not my issue...
--
Foxy


"Fox" wrote:

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #7  
Old October 4th, 2009, 06:20 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Split db and have backend on local PC

You can determine where the path should be:

If Dir("C:\Documents And Settings\" & GetUserID & "\My Jewlery Data\",
vbDirectory) = vbNullString Then

MkDir( "C:\Documents And Settings\" & GetUserID & "\My Jewlery Data")
End If

Here is the API call that will return the Windows user name of the user
currently logged in. Copy and Paste it into its own Standard Module.

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

lngresult = GetUserNameA(Buffer, Length)
If lngresult 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
GetUserID = UCase(userid)

End Function

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #8  
Old October 4th, 2009, 06:31 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

Not so brite with the vb code so sorry if this sounds stupid...

Where would the If Dir statement go, in global code? autoexec?

--
Foxy


"Klatuu" wrote:

You can determine where the path should be:

If Dir("C:\Documents And Settings\" & GetUserID & "\My Jewlery Data\",
vbDirectory) = vbNullString Then

MkDir( "C:\Documents And Settings\" & GetUserID & "\My Jewlery Data")
End If

Here is the API call that will return the Windows user name of the user
currently logged in. Copy and Paste it into its own Standard Module.

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

lngresult = GetUserNameA(Buffer, Length)
If lngresult 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
GetUserID = UCase(userid)

End Function

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #9  
Old October 4th, 2009, 06:44 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Split db and have backend on local PC

The code for the GetUserID function should go in a standard module by itself.
But, do not name a module the same name as any function or sub in the
module. My module is named GetUserAPI

As to the Dir and MkDir part, it will depend on where you are trying to set
up the directory. If it is in a form, it might be in the Click event of a
command button.

Perhaps if you can describe the procedure, we can give you a better answer.
--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

Not so brite with the vb code so sorry if this sounds stupid...

Where would the If Dir statement go, in global code? autoexec?

--
Foxy


"Klatuu" wrote:

You can determine where the path should be:

If Dir("C:\Documents And Settings\" & GetUserID & "\My Jewlery Data\",
vbDirectory) = vbNullString Then

MkDir( "C:\Documents And Settings\" & GetUserID & "\My Jewlery Data")
End If

Here is the API call that will return the Windows user name of the user
currently logged in. Copy and Paste it into its own Standard Module.

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

lngresult = GetUserNameA(Buffer, Length)
If lngresult 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
GetUserID = UCase(userid)

End Function

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

  #10  
Old October 4th, 2009, 07:00 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db and have backend on local PC

I'm uncertain what procedure you mean so I'll just try to explain how the
person gets the db

I have a Access DB My Jewelry Projects that I want to distribute to
individuals that want it....

So originally I had a backup and restore procedure however the code will not
run if the solution is packaged with access runtime so thus the reason for
the splitting of the db.

My thought is that if I split the db and package the front end in one
package and the backend in one package the uninstall of the front end to
install an upgrade will not affect the backend tables and the user will have
their data. So on upgrade only the front end would be upgraded....there are
problems with this but it's will have to do at the moment.

When I split the db of course it does not have a relative path so if I go
install it on another computer then the front end can not find the back end.

This means the the switchboard will not even open..no form is going to open.
The user does not have access to the navigation window for tables etc...

The procedure would be that the user would install the front end from the
exe file and then the backend from another exe file. Once installed it needs
to know where the back end is. Most users will not have too many computer
skills as well so things can not be too complicated...

Did I answer or am I just talking non sence...S
Thanks for your time I do appriciate it!


--
Foxy


"Klatuu" wrote:

The code for the GetUserID function should go in a standard module by itself.
But, do not name a module the same name as any function or sub in the
module. My module is named GetUserAPI

As to the Dir and MkDir part, it will depend on where you are trying to set
up the directory. If it is in a form, it might be in the Click event of a
command button.

Perhaps if you can describe the procedure, we can give you a better answer.
--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

Not so brite with the vb code so sorry if this sounds stupid...

Where would the If Dir statement go, in global code? autoexec?

--
Foxy


"Klatuu" wrote:

You can determine where the path should be:

If Dir("C:\Documents And Settings\" & GetUserID & "\My Jewlery Data\",
vbDirectory) = vbNullString Then

MkDir( "C:\Documents And Settings\" & GetUserID & "\My Jewlery Data")
End If

Here is the API call that will return the Windows user name of the user
currently logged in. Copy and Paste it into its own Standard Module.

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

lngresult = GetUserNameA(Buffer, Length)
If lngresult 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
GetUserID = UCase(userid)

End Function

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I will not know the user name folder....that's why I thought a relative path
would be \\My Documents\bla\bla.file
--
Foxy


"Klatuu" wrote:

It is not quite that simple. First, your syntax is incorrect using the
forward slash /

Also, you need to fully qualify the path. "My Documents" folder is a
subfolder of your user name folder. For example, my user name folder is
DaveH, so to get to My Documents, the full path is:

C:\Documents And Settings\DaveH\MyDocuments

Also, the path has to exist. If it does not, you can use MKDir to create
the path. To determine if it exists, use the Dir() function.

--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

I want to split my db for other reason than to share with tons of people and
I need the backend on the local machine with the front end but in a
different directory. The db front and backend will be
distributed as a packaged solution to a singler user. The user will install
the access runtime as well as the db's.
When I try to split the db called My Jewelry Project and place the backend
in a folder called My Jewelry Data using this address //My Document/My
Jewelry Data/JeweryData.accdb
it will not find the location....even though the folder exists....

I've tried ~/bla bla bla and ~//bla bla bla and these links do not work
either.

So how do I split a db and have the backend on the same computer with a
relative path?
Thanks,
--
Foxy

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.