473,373 Members | 1,170 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

Application autolinking to 3 database back-ends?


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
3 1794
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Keith G. Murphy | last post by:
I'm trying to get a feel for what most people are doing or consider best practice. Given a mod_perl application talking to a PostgreSQL database on the same host, where different users are...
22
by: Jordan S. | last post by:
SQL Server will be used as the back-end database to a non trivial client application. In question is the choice of client application: I need to be able to speak intelligently about when one...
38
by: kavsak | last post by:
Hi. Not sure that this is the right place to ask but here goes. I have an application based on access97 and VB6 which I need to upgrade. It has to handle up to 20 concurrent users on a Win2k...
6
by: Stan | last post by:
I am working on a database in ACCESS 2003. This is a simple DB with only one table. I have split the DB so I can upgrade and debug the front end before installing on my clients' computer. I used...
3
by: Sylvie | last post by:
My Windows Application has two forms, one form contains a grid (lets say Stock Listing), and the other is a form of one stock, contains some edit boxes for one stock's fields.. Is it possible to...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.