By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Application autolinking to 3 database back-ends?

P: n/a

I have a front-end application called, “inbusiness.mdb” and three
back-ends
databases called,
“inbusinessClient_be.mdb”, “inbusinessFund_be.mdb”, and,
“inbusiness_be”. I
created three back-ends for the potential intensive use of certain areas
of
the front-end application to their back-end tables by certain type of
organizations. With the code below, (used to make future front-end
updates
easier) I have experienced no problems to automatically link to a single
back-end database file whenever my software application was installed on
a
client’s computer.

While the installation routine defines the location, (C:\Program
Files\InBusiness\) for the front and back-ends; I need to know if the
same
code will automatically find ALL the back-ends, and not just the
original,
“inbusiness_be.mdb” database file.

Option Compare Database
Option Explicit
Private Declare Function apiSearchTreeForFile Lib "ImageHlp.dll" Alias _
"SearchTreeForFile" (ByVal lpRoot As String, ByVal lpInPath _
As String, ByVal lpOutPath As String) As Long
Public Function RefreshLinks()
On Error GoTo ErrorHandler
Dim objCat As New ADOX.Catalog 'Define the ADOX Catalog Object
Dim objTbl As ADOX.Table 'Define the ADOX Table Object
Dim strSearchFolder As String 'Folder to Search in.
Dim strFilename As String 'Db Name of the Linked Table
Dim strFullName As String 'Path & DB Name of the Linked Table.
Dim strSearchFile As String 'The new path of the database.
Dim blnTablesNotLinked As Boolean 'Determines if links are valid
'Open the catalog Microsoft ADO extensibility library
objCat.ActiveConnection = CurrentProject.Connection
'Loop through the table collection and refresh the linked tables.
For Each objTbl In objCat.Tables
' Check to make sure the table is a linked table.
If objTbl.Type = "LINK" Then
strFullName = objTbl.Properties("Jet OLEDB:Link Datasource")
strFilename = Mid$(strFullName, InStrRev(strFullName, "\", _
Len(strFullName)) + 1, Len(strFullName))
strSearchFolder = CurrentProject.Path
'The following line of code attempts to refresh the link.
'If the source cannot be found an error is generated.
'Please note that this code only checks one table to determine
'whether or not the links are valid.
objTbl.Properties("Jet OLEDB:Link Datasource") = strFullName
If blnTablesNotLinked = False Then
Exit Function
Else
'Set the search path to the path of the current project.
'The assumption is that the linked tables are located in subfolders.
strSearchFile = SearchFile(strFilename, strSearchFolder)
objTbl.Properties("Jet OLEDB:Link Datasource") = strSearchFile
End If
End If
Next
MsgBox "The InBusiness Data links were successfully refreshed!!! "
ExitHandler:
Exit Function
ErrorHandler:
Select Case Err.Number
Case -2147467259
blnTablesNotLinked = True
Resume Next
Case Else
MsgBox Err.Description & "Check to see if you installed the tables used
for
storing data is in the default directory. " & Err.Number
Resume ExitHandler
End Select
End Function
Private Function SearchFile(ByVal strFilename As String, _
ByVal strSearchPath As String) As String
On Error GoTo ErrLine
'Search the folder for first occurrence of the source databases.
Dim strBuffer As String
Dim lngResult As Long
SearchFile = "private"
strBuffer = String$(1024, 0)
lngResult = apiSearchTreeForFile(strSearchPath, strFilename, strBuffer)
If lngResult <0 Then
If InStr(strBuffer, vbNullChar) 0 Then
SearchFile = Left$(strBuffer, InStr(strBuffer, vbNullChar) - 1)
End If
End If
ExitLine:
Exit Function
ErrLine:
Resume ExitLine
End Function
'This function checks the first linked table in the database to
determine if
the links are valid. If the links 'are 'not valid, he function searches
for
the database and refreshes the links.

thanks,
John
*** Sent via Developersdex http://www.developersdex.com ***
Jul 31 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
John Phelan-Cummings wrote:
While the installation routine defines the location, (C:\Program
Files\InBusiness\) for the front and back-ends; I need to know if the
same
code will automatically find ALL the back-ends, and not just the
original,
"inbusiness_be.mdb" database file.
Did you write the code? If you did then one might guess that you know
the answer to the question.

If not, why not ask the person who wrote the code? I assume that as you
seem to be using it in a commercail application that you have his/her
pemrission to use it and an e-mail to that person would probably clear
up your concerns.

What does " Please note that this code only checks one table to
determine
whether or not the links are valid." mean?

Have you tested the code with more than one backend?

Aug 1 '06 #2

P: n/a
This code was shared with me along with another version some years ago;
how I find the person, I don't know. However, I've seen similar code
in VBA books since; so it's hardly that propritary. When I adapted the
code for my application I never dreamed of splitting the back-end. The
way I presented the issue at the time in the newsgroup was that I was
concerned about an end-user installing my software in an area other
than the default location (not a good idea in my opinion). So the
person, who provided me the first version, rewrote it so that when I
was ready to provide up dates; the updated front-end would find the
back-end, if placed in another directory other than the default.

I thought that the Access developer was quite generous. Yes, I
probably will test the code with multiple back-ends. However, I
thought that I would get a second opion first from someone I didn't
have to explain all this too.

John

P.S.: Oh yes, feel free to use the sample code:

Lyle Fairfield wrote:
John Phelan-Cummings wrote:
While the installation routine defines the location, (C:\Program
Files\InBusiness\) for the front and back-ends; I need to know if the
same
code will automatically find ALL the back-ends, and not just the
original,
"inbusiness_be.mdb" database file.

Did you write the code? If you did then one might guess that you know
the answer to the question.

If not, why not ask the person who wrote the code? I assume that as you
seem to be using it in a commercail application that you have his/her
pemrission to use it and an e-mail to that person would probably clear
up your concerns.

What does " Please note that this code only checks one table to
determine
whether or not the links are valid." mean?

Have you tested the code with more than one backend?
Aug 1 '06 #3

P: n/a
"jphelan" <jp*********@hotmail.comwrote in
news:11**********************@i3g2000cwc.googlegro ups.com:
When I adapted the
code for my application I never dreamed of splitting the back-end.
The way I presented the issue at the time in the newsgroup was
that I was concerned about an end-user installing my software in
an area other than the default location (not a good idea in my
opinion). So the person, who provided me the first version,
rewrote it so that when I was ready to provide up dates; the
updated front-end would find the back-end, if placed in another
directory other than the default.
I think it's ridiculous to hardwire the installation location of an
application, especially in the programs folder, which is restricted
for user-level logons. If your users are logged on as Administrators
(as so many Windows users stupidly are), that won't be a problem,
but if they are running a properly configured Windows box, they
won't have anything but READ access to the programs folder, which
will make your application unusable.

You could make your installation dynamic by using the user's
profile, which can be retrieved from environment variables (there is
a VBA command for that).

You could also provide an assumed location for the data in the same
folder as the front end, and if it's found there, automatically
relink. If it's not, present a FILE OPEN dialog to the user.

You might want to look at my RECONNECT utility, which makes it easy
to relink in applications with multiple back ends (i.e., the app
users more than one back end in daily operation):

http://www.dfenton.com/DFA/download/...Reconnect.html

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 1 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.