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
  #11  
Old October 4th, 2009, 07:26 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Split db and have backend on local PC

You are spot on about splitting so updates are easier.

One approach would be to put both the FE and the BE in the same path.
Then when the user opens the database, you can check to see if the FE is
pointing to a valid BE, if not, you can use the CurrentProject.FullName
method to determine where it is and relink the tables.
--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

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

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

Grinning
I am glad that I am spot on about something!!!! LOL

Okay so I would create each package and place the FE and BE in the same
folder then I would create a CurrentProject.FullName Method...
hm...
It's a bit above me I guess..I do not have a clue. I looked up what this is
but don't know how to use it or where it would go or even what it does...S

--
Foxy


"Klatuu" wrote:

You are spot on about splitting so updates are easier.

One approach would be to put both the FE and the BE in the same path.
Then when the user opens the database, you can check to see if the FE is
pointing to a valid BE, if not, you can use the CurrentProject.FullName
method to determine where it is and relink the tables.
--
Dave Hargis, Microsoft Access MVP


"Fox" wrote:

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

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

Okay I almost have it, thanks...
I created a form (form1) put the first bit of code behind it then created a
module called MainMod and added then next bit of code I found. I load the FE
and BE in correct folders on a different PC and most buttons on the
switchboard can find the BE...however there are two buttons that call for
forms that are still looking for the User/My Document/My Jewelry Projects/My
Jewelry Data.accdb file....
What could cause only two buttons not to be able to find the BE correct path?


Private Sub Form_Load()

Form_Form1.Visible = False

Dim Result As Boolean

Dim myCurrentPath As String

myCurrentPath = GetSpecialFolder(CSIDL_MY_DOCUMENTS)

'mycurrentpath = application.
'sample call:
Result = LinkTables(myCurrentPath & "\My Jewelry Projects\My Jewelry
Data.accdb")

If Result = True Then

'MsgBox ("DB Links successfully refreshed!")
DoCmd.OpenForm "SwitchBoard"
DoCmd.Close acForm, "Form1"


Else

MsgBox ("DB Links refresh failed!")


End If


End Sub

MainMon Code:

Option Compare Database
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
' Windows API Declares
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Private Declare Function GetCurrentThread Lib "kernel32" () As Long

Private Declare Function GetCurrentProcess Lib "kernel32" () As Long

Private Declare Function OpenProcessToken Lib "advapi32.dll" ( _
ByVal ProcessHandle As Long, _
ByVal DesiredAccess As Long, _
ByRef TokenHandle As Long) As Long

Private Declare Function SHGetFolderPath Lib "shell32.dll" Alias
"SHGetFolderPathA" ( _
ByVal HWnd As Long, _
ByVal csidl As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal pszPath As String) As Long

Private Declare Function GetUserProfileDirectory Lib "userenv.dll" Alias
"GetUserProfileDirectoryA" ( _
ByVal hToken As Long, _
ByVal lpProfileDir As String, _
ByRef lpcchSize As Long) As Long

Private Declare Function FormatMessage Lib "kernel32" _
Alias "FormatMessageA" ( _
ByVal dwFlags As Long, _
ByVal lpSource As Any, _
ByVal dwMessageId As Long, _
ByVal dwLanguageId As Long, _
ByVal lpBuffer As String, _
ByVal nSize As Long, _
ByRef Arguments As Long) As Long

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''
' Misc Constants
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''
Private Const MAX_PATH = 260&
Private Const S_OK = 0&
Private Const E_INVALIDARG As Long = &H80070057
Private Const S_FALSE As Long = &H1 ' odd but true that S_FALSE would be 1.


'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
' Used By OpenProcessToken and OpenProcess
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Private Const TOKEN_QUERY As Long = &H8
Private Const TOKEN_QUERY_SOURCE As Long = &H10
Private Const READ_CONTROL As Long = &H20000
Private Const STANDARD_RIGHTS_READ As Long = (READ_CONTROL)
Private Const TOKEN_READ As Long = (STANDARD_RIGHTS_READ Or TOKEN_QUERY)
Private Const SYNCHRONIZE = &H100000
Private Const PROCESS_VM_READ As Long = (&H10)
Private Const PROCESS_QUERY_INFORMATION As Long = (&H400)



'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
' used by FormatMessage
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
Private Const FORMAT_MESSAGE_ALLOCATE_BUFFER = &H100
Private Const FORMAT_MESSAGE_ARGUMENT_ARRAY = &H2000
Private Const FORMAT_MESSAGE_FROM_HMODULE = &H800
Private Const FORMAT_MESSAGE_FROM_STRING = &H400
Private Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000
Private Const FORMAT_MESSAGE_IGNORE_INSERTS = &H200
Private Const FORMAT_MESSAGE_MAX_WIDTH_MASK = &HFF
Private Const FORMAT_MESSAGE_TEXT_LEN = &HA0


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
' CSIDL Constants of various folder names.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Public Const CSIDL_ADMINTOOLS As Long = &H30
Public Const CSIDL_ALTSTARTUP As Long = &H1D
Public Const CSIDL_APPDATA As Long = &H1A
Public Const CSIDL_BITBUCKET As Long = &HA
Public Const CSIDL_COMMON_ADMINTOOLS As Long = &H2F
Public Const CSIDL_COMMON_ALTSTARTUP As Long = &H1E
Public Const CSIDL_COMMON_APPDATA As Long = &H23
Public Const CSIDL_COMMON_DESKTOPDIRECTORY As Long = &H19
Public Const CSIDL_COMMON_DOCUMENTS As Long = &H2E
Public Const CSIDL_COMMON_FAVORITES As Long = &H1F
Public Const CSIDL_COMMON_PROGRAMS As Long = &H17
Public Const CSIDL_COMMON_STARTMENU As Long = &H16
Public Const CSIDL_COMMON_STARTUP As Long = &H18
Public Const CSIDL_COMMON_TEMPLATES As Long = &H2D
Public Const CSIDL_CONNECTIONS As Long = &H31
Public Const CSIDL_CONTROLS As Long = &H3
Public Const CSIDL_COOKIES As Long = &H21
Public Const CSIDL_DESKTOP As Long = &H0
Public Const CSIDL_DESKTOPDIRECTORY As Long = &H10
Public Const CSIDL_DRIVES As Long = &H11
Public Const CSIDL_FAVORITES As Long = &H6
Public Const CSIDL_FLAG_CREATE As Long = &H8000
Public Const CSIDL_FLAG_DONT_VERIFY As Long = &H4000
Public Const CSIDL_FLAG_MASK As Long = &HFF00&
Public Const CSIDL_FLAG_PFTI_TRACKTARGET As Long = CSIDL_FLAG_DONT_VERIFY
Public Const CSIDL_FONTS As Long = &H14
Public Const CSIDL_HISTORY As Long = &H22
Public Const CSIDL_INTERNET As Long = &H1
Public Const CSIDL_INTERNET_CACHE As Long = &H20
Public Const CSIDL_LOCAL_APPDATA As Long = &H1C
Public Const CSIDL_MYPICTURES As Long = &H27
Public Const CSIDL_NETHOOD As Long = &H13
Public Const CSIDL_NETWORK As Long = &H12
Public Const CSIDL_PERSONAL As Long = &H5 ' My Documents
Public Const CSIDL_MY_DOCUMENTS As Long = &H5
Public Const CSIDL_PRINTERS As Long = &H4
Public Const CSIDL_PRINTHOOD As Long = &H1B
Public Const CSIDL_PROFILE As Long = &H28
Public Const CSIDL_PROGRAM_FILES As Long = &H26
Public Const CSIDL_PROGRAM_FILES_COMMON As Long = &H2B
Public Const CSIDL_PROGRAM_FILES_COMMONX86 As Long = &H2C
Public Const CSIDL_PROGRAM_FILESX86 As Long = &H2A
Public Const CSIDL_PROGRAMS As Long = &H2
Public Const CSIDL_RECENT As Long = &H8
Public Const CSIDL_SENDTO As Long = &H9
Public Const CSIDL_STARTMENU As Long = &HB
Public Const CSIDL_STARTUP As Long = &H7
Public Const CSIDL_SYSTEM As Long = &H25
Public Const CSIDL_SYSTEMX86 As Long = &H29
Public Const CSIDL_TEMPLATES As Long = &H15
Public Const CSIDL_WINDOWS As Long = &H24
Public Const PRIV_PAL_FUNC As Long = &H0
Public Const CSIR_FUNC As Long = (PRIV_PAL_FUNC Or &HD)

Function LinkTables(DbPath As String) As Boolean
'This links to all the tables that reside in DbPath,
' whether or not they already reside in this database.
'This works when linking to an Access .mdb file, not to ODBC.
'This keeps the same table name on the front end as on the back end.
Dim rs As Recordset

On Error Resume Next

'get tables in back end database
Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
"FROM MSysObjects IN '" & DbPath & "' "
& _
"WHERE Type=1 AND Flags=0")
If Err 0 Then Exit Function

'link the tables
While Not rs.EOF
If DbPath Nz(DLookup("Database", "MSysObjects", "Name='" &
rs!Name & "' And Type=6")) Then
'delete old link, assuming front and back end table have the
same name
DoCmd.DeleteObject acTable, rs!Name
'make new link
DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath,
acTable, rs!Name, rs!Name
End If
rs.MoveNext
Wend
rs.Close

LinkTables = True
End Function



Public Function TrimToNull(Text As String) As String
Dim N As Long
N = InStr(1, Text, vbNullChar)
If N Then
TrimToNull = Left(Text, N - 1)
Else
TrimToNull = Text
End If
End Function





Public Function GetUserProfileFolder()

Dim Res As Long
Dim CurrentProcessHandle As Long
Dim TokenHandle As Long
Dim UserProfileDirectory As String
Dim LLen As Long
Dim Pos As Integer

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Initialize the string to receive the folder name
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
UserProfileDirectory = String(MAX_PATH, " ")
LLen = Len(UserProfileDirectory)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Get the pseudo-handle of the current process
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
CurrentProcessHandle = GetCurrentProcess()

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Open the access token of the process
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Res = OpenProcessToken(CurrentProcessHandle, TOKEN_READ, TokenHandle)
If Res = 0 Then
MsgBox "ERROR OpenProcessToken " & CStr(Err.LastDllError) & " " &
GetSystemErrorMessageText(Err.LastDllError)
Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Get the user's directory
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Res = GetUserProfileDirectory(TokenHandle, UserProfileDirectory, LLen)
If Res = 0 Then
CloseHandle CurrentProcessHandle
CloseHandle TokenHandle
MsgBox "ERROR GetUserProfileDirectory " & CStr(Err.LastDllError) & " "
& GetSystemErrorMessageText(Err.LastDllError)
Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Trim to null char
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
UserProfileDirectory = TrimToNull(Text:=UserProfileDirectory)


'''''''''''''''''''''''''''''''''''''''''''''''''' ''
'Close handles
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
CloseHandle CurrentProcessHandle
CloseHandle TokenHandle

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Return the result
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
GetUserProfileFolder = UserProfileDirectory

End Function





Public Function GetSpecialFolder(FolderCSIDL As Long) As String

Dim HWnd As Long
Dim Path As String
Dim Res As Long
Dim ErrNumber As Long
Dim ErrText As String

''''''''''''''''''''''''''''''''''''''''''''
' initialize the path variable
''''''''''''''''''''''''''''''''''''''''''''
Path = String$(MAX_PATH, vbNullChar)

''''''''''''''''''''''''''''''''''''''''''''
' get the folder name
''''''''''''''''''''''''''''''''''''''''''''
Res = SHGetFolderPath(HWnd:=0&, _
csidl:=FolderCSIDL, _
hToken:=0&, _
dwFlags:=0&, _
pszPath:=Path)
Select Case Res
Case S_OK
Path = TrimToNull(Text:=Path)
GetSpecialFolder = Path
Case S_FALSE
MsgBox "The folder code is valid but the folder does not exist."
GetSpecialFolder = vbNullString
Case E_INVALIDARG
MsgBox "The value of FolderCSIDL is not valid."
GetSpecialFolder = vbNullString
Case Else
ErrNumber = Err.LastDllError
ErrText = GetSystemErrorMessageText(Res)
MsgBox "An error occurred." & vbCrLf & _
"System Error: " & CStr(ErrNumber) & vbCrLf & _
"Description: " & ErrText
End Select

End Function

Private Function GetSystemErrorMessageText(ErrorNumber As Long) As String
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''
' GetSystemErrorMessageText
'
' This function gets the system error message text that corresponds
' to the error code parameter ErrorCode. This value is the value returned
' by Err.LastDLLError or by GetLastError, or occasionally as the returned
' result of a Windows API function.
'
' These are NOT the error numbers returned by Err.Number (for these
' errors, use Err.Description to get the description of the error).
'
' In general, you should use Err.LastDllError rather than GetLastError
' because under some circumstances the value of GetLastError will be
' reset to 0 before the value is returned to VBA. Err.LastDllError will
' always reliably return the last error number raised in an API function.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''

Dim ErrorText As String
Dim TextLen As Long
Dim FormatMessageResult As Long
Dim LangID As Long

''''''''''''''''''''''''''''''''
' initialize the variables
''''''''''''''''''''''''''''''''
LangID = 0& 'default language
ErrorText = String$(FORMAT_MESSAGE_TEXT_LEN, vbNullChar)
TextLen = FORMAT_MESSAGE_TEXT_LEN

' Call FormatMessage to get the text of the error message text
' associated with ErrorNumber.
FormatMessageResult = FormatMessage( _
dwFlags:=FORMAT_MESSAGE_FROM_SYSTEM Or _
FORMAT_MESSAGE_IGNORE_INSERTS, _
lpSource:=0&, _
dwMessageId:=ErrorNumber, _
dwLanguageId:=LangID, _
lpBuffer:=ErrorText, _
nSize:=TextLen, _
Arguments:=0&)
If FormatMessageResult = 0& Then
' An error occured. Display the error number, but
' don't call GetSystemErrorMessageText to get the
' text, which would likely cause the error again,
' getting us into a loop.

MsgBox "An error occurred with the FormatMessage" & _
" API functiopn call. Error: " & _
CStr(Err.LastDllError) & _
" Hex(" & Hex(Err.LastDllError) & ")."
GetSystemErrorMessageText = vbNullString
Exit Function
End If

' If FormatMessageResult is not zero, it is the number
' of characters placed in the ErrorText variable.
' Take the left FormatMessageResult characters and
' return that text.
ErrorText = Left$(ErrorText, FormatMessageResult)
GetSystemErrorMessageText = ErrorText

End Function




  #14  
Old October 5th, 2009, 04:04 PM posted to microsoft.public.access.tablesdbdesign
Elza Kaska
external usenet poster
 
Posts: 2
Default Backend install question

Your problem may lie in the pathing involved with the My documents directory structure. If you are developing on your machine, you directory structure may be something like this and your installation directory may be different. example:
C:\Documents and Settings\John Doe directory\My Documents
your target may be:
C:\Documents and Settings\Willie Bethere directory\My Documents

What I do is link in the tables from the back end once I have installed the front and back ends. That way it points via Microsoft directory structures to the right pathing.

Hope this helps.



Fox wrote:

Split db and have backend on local PC
04-Oct-09

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

EggHeadCafe - Software Developer Portal of Choice
ASP.NET DropDown ListBox and XML Databinding
http://www.eggheadcafe.com/tutorials...n-listbox.aspx
  #15  
Old October 5th, 2009, 04:04 PM posted to microsoft.public.access.tablesdbdesign
Elza Kaska
external usenet poster
 
Posts: 2
Default Split db

Your problem may lie in the pathing involved with the My documents directory structure. If you are developing on your machine, you directory structure may be something like this and your installation directory may be different. example:
C:\Documents and Settings\John Doe directory\My Documents
your target may be:
C:\Documents and Settings\Willie Bethere directory\My Documents

What I do is link in the tables from the back end once I have installed the front and back ends. That way it points via Microsoft directory structures to the right pathing.

Hope this helps.



Fox wrote:

Split db and have backend on local PC
04-Oct-09

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

EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 3.0 Design Patterns [O'Reilly] - Judith Bishop
http://www.eggheadcafe.com/tutorials...-30-desig.aspx
  #16  
Old October 5th, 2009, 04:25 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db

Yes, this would work but the new user will not have access to relink tables.
So that is the reason for all the code. For some reason when you split the
tables you can only name a network location and you can not name a relative
path.....Got it almost working but one table does not want to
co-operate....the rest of the tables are responding to the code found but
this one little ugly step child will not co-operate! S
Thanks for your help.
Janet
--
Foxy


"Elza Kaska" wrote:

Your problem may lie in the pathing involved with the My documents directory structure. If you are developing on your machine, you directory structure may be something like this and your installation directory may be different. example:
C:\Documents and Settings\John Doe directory\My Documents
your target may be:
C:\Documents and Settings\Willie Bethere directory\My Documents

What I do is link in the tables from the back end once I have installed the front and back ends. That way it points via Microsoft directory structures to the right pathing.

Hope this helps.



Fox wrote:

Split db and have backend on local PC
04-Oct-09

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

EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 3.0 Design Patterns [O'Reilly] - Judith Bishop
http://www.eggheadcafe.com/tutorials...-30-desig.aspx

  #17  
Old October 5th, 2009, 06:20 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default Split db

On Mon, 5 Oct 2009 08:25:02 -0700, Fox
wrote:

Yes, this would work but the new user will not have access to relink tables.
So that is the reason for all the code. For some reason when you split the
tables you can only name a network location and you can not name a relative
path.....Got it almost working but one table does not want to
co-operate....the rest of the tables are responding to the code found but
this one little ugly step child will not co-operate! S
Thanks for your help.
Janet


Hi Janet,

If you want a more automated approach to relinking tables, you're
welcome to use our free J Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

That last part about auto-relinking to a local back-end might help
you. It isn't exactly what you want, because it links to the *same*
folder as your application, but you may find it useful.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #18  
Old October 5th, 2009, 06:34 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db

Cool THANK YOU...
I will look at it...
and accually the FE and BE are in the same folder....with the code I've
placed accually most tables get mapped correctly on another PC but there is
one table that has 2 attachment fields and this table will not mapp to save
my life...I think it has something to do with the attachment field as I tried
the same code on another db and I had the exact same problem table with the
attachement field will not mapp...
I was about to do a test with a db with no attachment fields just to confirm.
Thanks again, I was starting to wonder if my questions were just too stupid
to answer which is the case sometime. grin
--
Foxy


"Armen Stein" wrote:

On Mon, 5 Oct 2009 08:25:02 -0700, Fox
wrote:

Yes, this would work but the new user will not have access to relink tables.
So that is the reason for all the code. For some reason when you split the
tables you can only name a network location and you can not name a relative
path.....Got it almost working but one table does not want to
co-operate....the rest of the tables are responding to the code found but
this one little ugly step child will not co-operate! S
Thanks for your help.
Janet


Hi Janet,

If you want a more automated approach to relinking tables, you're
welcome to use our free J Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

That last part about auto-relinking to a local back-end might help
you. It isn't exactly what you want, because it links to the *same*
folder as your application, but you may find it useful.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com


  #19  
Old October 5th, 2009, 06:58 PM posted to microsoft.public.access.tablesdbdesign
fox
external usenet poster
 
Posts: 65
Default Split db

Absolutly fantastic! THANK YOU!!!! It worked, I did have to search for the
folder even though the db BE was in the Same folder as the FE but once I
found it then presto majico....

I do get a macro failure for jstchecktablelinks_Full() I will read further
to if I can figure that one out...
--
Foxy


"Armen Stein" wrote:

On Mon, 5 Oct 2009 08:25:02 -0700, Fox
wrote:

Yes, this would work but the new user will not have access to relink tables.
So that is the reason for all the code. For some reason when you split the
tables you can only name a network location and you can not name a relative
path.....Got it almost working but one table does not want to
co-operate....the rest of the tables are responding to the code found but
this one little ugly step child will not co-operate! S
Thanks for your help.
Janet


Hi Janet,

If you want a more automated approach to relinking tables, you're
welcome to use our free J Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

That last part about auto-relinking to a local back-end might help
you. It isn't exactly what you want, because it links to the *same*
folder as your application, but you may find it useful.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com


  #20  
Old October 22nd, 2009, 09:04 PM posted to microsoft.public.access.tablesdbdesign
Kathy R.
external usenet poster
 
Posts: 20
Default Split db and have backend on local PC

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.


Just thought I'd point out that Windows Vista does not use the
"Documents and Settings" folder. It is now the "Users" folder. It
appears that you've been pointed to a solution no longer uses that path
so this may be a moot point, but just in case...

Kathy R.
 




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 10:01 AM.


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