I've tested many ways to accomplish this, but none of the code I've found works quite right. I am using the code written by Dev Ashish but it replicates some of the functionality I already have. And, if you change the path to the back end manually and launch the db, Dev's code throws an error.
I think this request is fairly straightforward for someone experienced. I'm sure the code exists for this somewhere but I can't find it. Using Access 2007.
I have a split db setup and also another table that exists as a linked table. I've created forms (Form1, Form2) that allow an end user to change paths to these linked db's and this bit works fine. I have a startup form ready to accept code to check the following two items:
One of my linked back-end tables is called TblDepartment. I would like to check if this linked table exists and is accessible during startup. No need to iterate through all the tables, so I arbitrarily picked TblDepartment. If not-accessible, then open Form#1.
Next, the other stand alone linked mdb (not part of the BE db), has the same field names as one of my local tables and joins my main db via a union query. Again, I would like to check to see that this db is linked properly on startup. If not-accessible, then open Form#2.
As a side note, my db which I inherited from another developer has mixed references to DAO and ADODB. Would it be beneficial to change all code to one or the other?
Thanks for the look.
6 28847 @tuxalot
I wrote this little code snippet for you. Hopefully, it should point you in the right direction. Any questions, please feel free to ask. - Private Sub cmdTestLink_Click()
-
On Error GoTo Err_cmdTestLink_Click
-
Const conLINKED_TABLE As String = "tblDepartment"
-
-
'A Linked Table will have a Connect Strain whose Length is > 0
-
If Len(CurrentDb.TableDefs(conLINKED_TABLE).Connect) > 0 Then
-
'OK, we know that conLINKED_TABLE is a Linked Table, but is the Link valid?
-
'The next line of code will generate Errors 3011 or 3024 if it isn't
-
CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink
-
'If you get to this point, you have a valid, Linked Table
-
'...normal code processing here
-
Else
-
'An Internal Table will have a Connect String Length of 0
-
MsgBox "[" & conLINKED_TABLE & "] is a Non-Linked Table", vbInformation, "Internal Table"
-
End If
-
-
Exit_cmdTestLink_Click:
-
Exit Sub
-
-
Err_cmdTestLink_Click:
-
Select Case Err.Number
-
Case 3265
-
MsgBox "[" & conLINKED_TABLE & "] does not exist as either an Internal or Linked Table", _
-
vbCritical, "Table Missing"
-
Case 3011, 3024 'Linked Table does not exist or DB Path not valid
-
MsgBox "[" & conLINKED_TABLE & "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
-
Case Else
-
MsgBox Err.Description & Err.Number, vbExclamation, "Error in cmdTestLink_Click()"
-
End Select
-
Resume Exit_cmdTestLink_Click
-
End Sub
Spot on! Thanks ADezii.
Tux
Actually if you manually change the path to the linked db and open the app, it throws a 3044 error. I added this to Err_cmdTestLink_Click and it works nicely.
@tuxalot
Error Number 3044 is 'Application-defined or object-defined error' which hardly seems applicable in this case. Not that it really matters, but is there also some auxiliary code that you have not mentioned?
Don't think so...where would I find the auxiliary code you are referring to?
@tuxalot
Not importatnt, tuxalot, just getting side-tracked.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: pinballjim |
last post by:
Hello everyone,
I'm looking for a simple way to create a local copy of a linked table.
I've got a database that links about 10 tables from other databases.
This works fine on my machine, but I...
|
by: Sam DeRT |
last post by:
Is there a way to hard code what a path to a linked table would be
without
going through the Linked Table Manager or a re-linking process?
My issue is that I'm installing a database with linked...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For...
|
by: Jill Elaine |
last post by:
I am building an Access 2002 frontend with linked tables to an
encrypted Paradox 7 database. When I first create these linked tables,
I'm asked for the password to the encrypted Paradox database,...
|
by: Andy |
last post by:
Hello, I am working on an Access Application that uses Linked Tables to
a SQL Server Backend. The problem I have is that there will be 2 types
of users, one which will use a trusted connection and...
|
by: bubbles |
last post by:
Using Access 2003 front-end, with SQL Server 2005 backend.
I need to make the front-end application automatically refresh the
linked
SQL Server tables.
New tables will be added dynamically in...
|
by: redpears007 |
last post by:
Hi all, can anyone help me with this one?
I have a database that has multiple linked tables and i want some code
to tell the database on startup to do first check for the tables in a
folder on...
|
by: afromanam |
last post by:
Whew..
OK, running access 2003 in win xpsp2 and access 2007 in win vista.
This is the question...
We have a db, split in front end (FE.mdb) and backend (BE.mdb)
FE has tons of linked...
|
by: Airtech |
last post by:
I am building a media management tool and starting it in Access 2003.
I have various linked tables, and have sufficient code to loop through my list of linked tables to verify that the data file...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |