Compact Database Please read path issue 2

[cyberbiker]

I am almost through with a pretty major database program (Access97). The help, (direct and indirect) from these forums have made it possible for me to do this, since I have been learning database programming as I went.

At this point, the program works quite well and everyone is more or less pleased (the main man is quite pleased which is what counts I guess), but I have one more thing I need to improve upon, which is to repair and compact the database.

The following code works well, but I do not like hardcoding paths.

Private Sub cmdCompact_Click()
DBEngine.RepairDatabase "c:\my documents\aaaa\testbe.mdb"
DBEngine.CompactDatabase "c:\my documents\aaaa\testbe.mdb", "c:\my documents\aaaa\Newdb.mdb"
Kill "c:\my documents\aaaa\testbe.mdb"
Name "c:\my documents\aaaa\Newdb.mdb" As "c:\my documents\aaaa\testbe.mdb"
End Sub

In actual practice the backend will be on the server not my c drive.
I used the linked table manager to link to the backend.
Is there a logical way for me to improve this code?
Perhaps a way to read the path from the linked table manager? Or any other ideas?
Thanks Terry (cyberbiker)

 

[Escolar]

yes you can get the current path pretty easily.
Try this in the immediate window.
? CurrentDB.Name
This give the full path such as
H:\Database\Payroll\PayData.MDB

Here is a short function (not good coding standards - please be tolerant) to get the path itself from the above

Public Function GetPath(PathandName) As String
' Get path name up to but not including last "\"
Dim X As Integer, y As Integer
Do
X = y
y = InStr(X + 1, PathandName, "\&quot
Loop Until y = 0
GetPath = Left(PathandName, X - 1)
End Function

You could get the current path by calling it like this.
MSGBOX "Current path is: " & GetPath(CurrentDB.Name)

I hope this helps.
It's a beautiful day here in Adelaide, South Australia.

 

[cyberbiker]

Would love to get to Australia some time. Looks like great country to ride in. Of course, I have seen less than 1/2 of the USA on a bike so far and I am now 50. Working sure interferes with my life
Your post was very helpful, but I do not think I was clear. (Or else, I am not thinking straight yet this early morning in Western Pennsylvannia, USA). I need to find the path to the linked tables not the front end. Would not this return the path for the front end on the users pc?
I am not even certain that there is any real logic behind wanting to do this. Since I use the linked table manager to link the back end, I would have to relink and create a new MDE and redistribute this to the users anyway if they move want to move the back end.
It just bugs me to see anything hard coded. Terry (cyberbiker)

 

[Escolar]

Oops, sorry I misunderstood.
I suspect you will need to change your connect string using code.
Here is a function which lists all linked tables in the current database.

Public Function ListConnections() As Boolean
' Displays the linked tables on the current database.
'
Dim gDB As Database, gTD As TableDef
Set gDB = CurrentDb
MsgBox "List of linked tables in " & CurrentDb.Name
For Each gTD In gDB.TableDefs
If gTD.Connect <> &quot;&quot; Then
MsgBox &quot;Local Name: &quot; & gTD.Name _
& Chr(13) & &quot;Remote Name: &quot; & gTD.SourceTableName _
& Chr(13) & &quot;Connection String: &quot; & gTD.Connect
End If
Next
End Function

Run it by entering ? ListConnections in the immediate window.
Now what you will need to do is set up a table to read the new connections that you want for you production user.
Then loop through the tables as above and open your table of connections

You refresh by entering
gTD.Connect = &quot; new connection data&quot;
and
gTD.RefreshLink

Where &quot; new connection data &quot; comes from a local stored or some other source.
It's certainly possible to do this and highly automate it too. - I do it on a regular basis.

One of my Access databases is called SBA_DEV, which contains all objects except tables, linked to SBA_DEV_DATA which has tables only. When I copy SBA_DEV to SBA_PROD, the opening macro will validate all links and change them to link to the tables in SBA_PROD_DATA by altering the Connect string. This way I can easily e-mail a new version of a project to my remote users. I also have a TEST version. This method easily handles the transition from DEV to TEST to PROD.

I hope this helps.

 

[cyberbiker]

This is great. Looks like what I will need.
I have the program running on the department heads pc now. Once he approves it, I will add this function and go from there with it.
I really appreciate the assistance. I know I do not explain things well at times and it is hard to follow what I am asking sometimes. Again, many thanks. Terry (cyberbiker)

 

[Escolar]

OK Glad to have helped.
Send me an e-mail on rvparkes@hotmail.com and I'll send you the complete routines as a ZIP file.

PS When I was 50, there was no such thing as visual basic, let alone Access 2, Access 97, Excel etc etc
Glad to hear you've cycled half the USA.

I lived in Manhattan for 7 years in the late sixties - loved it. I was there when the WTC was built!!