Table Linking Code Problem

[Robin]

I have used a bit of code that would check for the links to a BE datatable
and had replaced it recently because it used the commondialog feature which
became problematic when switching to access 2007. I began using Dev Ashish
fRefreshlinks which works fine when users must specify the location each time
the database is opened. Since this is not convenient I have tried to retain
the AreTablesAttached portion of the previous code to determine if the
fRefreshLinks code is needed. But everytime I change the name of the BE,
outside of Access in windows, to test the Must Locate Datatables feature.
The program just starts and immediately closes. Holding the shift button
down does not stop the autoexec feature. I am at a loss for where to check
next. The code testing the links to determine if the fRefrehlinks code is
required is:

Function AreTablesAttached() As Boolean
' Update connection information in attached tables.
'
' Number of attached tables for progress meter.
Const MAXTABLES = 50
Const NONEXISTENT_TABLE = 3011
Const DB_NOT_FOUND = 3024
Const ACCESS_DENIED = 3051
Const READ_ONLY_DATABASE = 3027

Dim intTableCount As Integer
Dim intResponse As Integer
Dim strFileName As String
Dim strAppDir As String
Dim vntReturnValue As Variant
Dim tdf As TableDef
Dim db As Database
Dim rst As Recordset

Dim strFilter As String
Dim strInputFileName As String

Set db = CurrentDb

' AreTablesAttached = True

' Continue if attachments are broken.
On Error Resume Next
' Open attached table to see if connection information is correct.
Set rst = db.OpenRecordset("InsuranceCompany")
' Exit if connection information is correct.
If Err.Number = 0 Then
AreTablesAttached = True
rst.Close
Exit Function
Else
AreTablesAttached = fRefreshLinks()


rst.Close
Exit Function


End If


End Function

and the AutoExec code is:


Function AutoExec()
On Error GoTo AutoExec_Err:

Dim fAnswer As Boolean
Dim dblStartTime As Double
Dim dblTimeElapsed As Double

'Open splash screen form
DoCmd.OpenForm "frmSplash"
DoEvents
'Invoke hourglass
DoCmd.Hourglass True
'Call routine that checks if tables are properly attached
fAnswer = AreTablesAttached()

'Test return value and proceed only if tables were
'successfully attached
If Not fAnswer Then
'fRefreshLinks
MsgBox "You Cannot Run This App Without Locating Data Tables"
DoCmd.Close acForm, "frmSplash"
'DoCmd.Close acForm, "frmGetTables"
Exit Function
End If
'Call GetCompanyInfo
DoCmd.Hourglass False
DoCmd.OpenForm "SwitchBoard"
DoCmd.Maximize
'If splash screen is still loaded, unload it
If IsLoaded("frmSplash") Then
DoCmd.Close acForm, "frmSplash"
End If
Exit Function

AutoExec_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Function
End Function


So the only way to make any tests are to rename the BE back to what was
previously linked. This has me stumped tremendously. Any thoughts would be
appreciated.

Thank you,
Robin

 

[a a r o n _ k e m p f]

wow-- that's just fantastic

In other words- you think that this whole backend thing is 'too
complex'.
That's funny-- I do also.

I personally choose to use a real database-- like SQL Server for
example-- because i can keep all my tables, queries in one place; and
I can change these while people are using the application.
Meanwhile, you're stuck maintaining obsolete DAO code??!!??

you shoud upsize to SQL Server and stop listening to these dorks
and stop using code off the internet just because some random dude
tells you to

 

[Peter Hibbs]

Robin,

What I do is create a small text file in the same folder as the FE
which holds the pathname to the BE, the re-link code in the FE file
then uses this to re-establish the links when a new FE is provided.
This means that the relinking is done automatically without the user
having to do anything. You may need to modify it slightly to add your
splash screen code though.

See http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=184
for an example.

HTH

Peter Hibbs.

 

[BruceM]

So the OP should listen to one random dude only (you) while ignoring the
rest? Considering that despite your contention DAO is not obsolete, other
information you provide must be regarded with some skepticism.

wow-- that's just fantastic

In other words- you think that this whole backend thing is 'too
complex'.
That's funny-- I do also.

I personally choose to use a real database-- like SQL Server for
example-- because i can keep all my tables, queries in one place; and
I can change these while people are using the application.
Meanwhile, you're stuck maintaining obsolete DAO code??!!??

you shoud upsize to SQL Server and stop listening to these dorks
and stop using code off the internet just because some random dude
tells you to

 

[Robin]

Thanks for pointing me in the right direction. I can see that the Checklinks
piece is a better solution than what I currently have. I'll see what luck I
have with the adaptation.

Thank you so much,

Robin

 

[Robin]

While the code that I replaced is more efficient than what I was using
previously. Thank you again.

The real problem was that since I created a custom ribbon in my access 2007
database and had it stored in the Back End datatable. When the BE could not
be found Access failed before the AutoExec code could run. Which is what
created the Front End not to open at all, shift key depressed or otherwise.

Thank you,
Robin

 

[a a r o n _ k e m p f]

Re:
So the OP should listen to one random dude only (you) while ignoring
the
rest? Considering that despite your contention DAO is not obsolete,
other
information you provide must be regarded with some skepticism.

YES, THE OP SHOULD LISTEN TO ME, THE ONLY CERTIFIED IT PROFESSIONAL IN
THIS GROUP

 

[a a r o n _ k e m p f]

DAO wasn't included in MDAC, Windows or Office for most of a decade.

Do you honestly let your vendors backtrack on shit like that?

ADO is superior to DAO for many reasons-- chief among them
portability, and performance.

If DAO is too slow, you need to rewrite it to a different DAL, for
example ADO or ADOMD.
If ADO isn't working for you, you merely change the connection string
from MS Access to SQL Server, or whatever other platform you want.

DAO is dead.
It has been for a long long long time.

-Aaron

 

[BruceM]

Certified I can believe, but not as an IT Professional.

Re:
So the OP should listen to one random dude only (you) while ignoring
the
rest? Considering that despite your contention DAO is not obsolete,
other
information you provide must be regarded with some skepticism.

YES, THE OP SHOULD LISTEN TO ME, THE ONLY CERTIFIED IT PROFESSIONAL IN
THIS GROUP

 

[Robin]

I am keeping the SQL option available for future use. Right now it would be
difficult to take advice from some one that was unable to answer my question
or help solve my problem. This is probably why most of us use the opinions
posted here.

Robin

 

[a a r o n _ k e m p f]

now -HOW- is it that I'm not answering your question?

you don't need to write DAO, you don't need to deal with any of this
crap.
If you want a truly 'plug and play' database environment, you should
move to 'Access Data Projects'.

That way- you woudln't have to deal with the hassle of FrontEnd /
BackEnd.

Logically-- you should keep your tables and queries where they belong-
on a database server.

Then you wouldn't have to write any code-- you wouldn't have to sync
or re-link.

Just because other people here are more popular-- it doesn't make me
wrong.
I'm the only certified DBA here.

Thanks

-Aaron Kempf