Compaction question

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

Hello,

I have a run-time MDE database with the front end on a pc
and the back end on a network. I can't know up front
what the path to the back end will be. I've included the
Compact and Repair tool in the database. How can I make
the Compact and Repair tool compact the back end, when I
don't know what the path to the back end will be? Thanks
in advance to anyone who replies.
 
This is an easy question.

You can't. Sorry. You have to know where the database is.

I do have to ask a probing question. If you don't know where the backend is
to compact it, how in the world does you app work? You had to know where it
was is to link the tables in your front-end.

If you're writing your own code, which I assume you must be if your app is a
run-time, you have a few options.

My favorite is to steal the path from one of the linked tables.

For the complete database name, try:
Mid(CurrentDb.TableDefs("NameOfLinkedTable").Connect, 11)

It doesn't matter which table you pick if all of the tables are in the same
backend.

You can see this work in the immediate window by putting a ? in front of the
line.

For just the path, add this function to a module:

<Code>
Function LinkedTablePath(pstrLinkedTableName As String) As String
On Error GoTo ErrorRoutine

Dim strFullDatabaseName As String

strFullDatabaseName =
Mid(CurrentDb.TableDefs(pstrLinkedTableName).Connect, 11) / 0
LinkedTablePath = Left(strFullDatabaseName, InStrRev(strFullDatabaseName,
"\"))

ProceedureExit:
Exit Function

ErrorRoutine:
MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, _
vbExclamation, "Routine Failure"

Resume ProceedureExit

End Function
</Code>

You can test the function in the immediate window by entering:
?LinkedTablePath("NameOfLinkedTable")

Another technique I use is to store the path once you know it (from linking)
in an ini file (archaic, but I still like it) or in the registry.

And you could always open the file open dialog box and drill for it.

No matter how you cut it, at some point you have to know where the backend
is.

Good luck.

Sco
 
Thanks for your answer, M.L. If I understand you
correctly, are you saying that the Compact and Repair
function will follow the table links to find the back end
and compact it? This is an Access point I'm just not
sure of. Thanks again very much for your help.
 
No, no, no.

I'm saying that you'll need to write some code to do this.

Sco
 
Back
Top