Attach/Reattach MDB

[BradW]

Hi,

I have an application that I wrote in Access97. Like many developers I have the code in one MDB and the data stored in an attached MDB. The 3rd party routine to attach or reattach the data MDB does not work under Access 2000 and I need to port this to Access 2000. Can anyone recommend a good 3rd party application that will check for the attachment and either get it reattached or let the user reattach it if they install in a directory other than the default. I'd like to do this with minimal coding and tweaking time. Thanks much for the help.

Brad

 

[rbowes]

What I would do if I were you is create an ODBC connection so that the backend is on a server and the user uses the frontend to connect through ODBC. If you are merely connecting tabledefs then after setting up the ODBC DSN you could relink your TableDefs on the startup. What you'll need:

1. set up the ODBC through the control panel ODBC Data Sources (each user will have to configure their ODBC connection on their computer.)
2.Create in the frontend a module that will do the relinking
and in the "Declarations" section of the module put something like this:
Public Const cODBCPATH As String = ODBC;DSN=dsnname;Description=;UID=sa;PWD=;DATABASE=databasename

3. Create a local table in the frontend called "tblLinks"
with two fields: a. TableName; b. SourceTableName
fill the table with the linked table name and the source table name (usually the same name if from an Access provider, but SQL server will place a dbo_ before each table name)

4. Create a Function called RefreshingLinks()
it might look something like this:

Public Funtion RefreshingLinks()as boolean
Dim db As DAO.Database
Dim i As Integer, tdf As DAO.TableDef
Dim tdfNew As New DAO.TableDef
Dim rec As DAO.Recordset
Dim tdfLinked As DAO.TableDef
Dim strtablename As String

On Error GoTo LinktoSQL_Err

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblLinks&quot
rec.MoveLast
rec.MoveFirst
For i = 1 To rec.RecordCount
strtablename = rec.Fields(0).Value
txbTableName.Value = Mid(strtablename, 5)
Set tdfNew = db.CreateTableDef(strtablename)
tdfNew.SourceTableName = rec.Fields(1).Value
tdfNew.Connect = cODBCPATH

db.TableDefs.Append tdfNew

rec.MoveNext
Next i
RefreshDatabaseWindow
LinktoSQL = True

LinktoSQL_Exit:
Exit Function
rec.close
set rec = nothing
LinktoSQL_Err:
Select Case Err.Number
Case 3010
db.TableDefs.Delete strtablename
db.TableDefs.Append tdfNew
Resume Next
Case Else
MsgBox Err.Number & Err.Description
LinktoSQL = False
Resume LinktoSQL_Exit
End Select
end Function

Place this as part of the startup routine and you should have no problems relinking.

Bob

 

[BradW]

Bob,

Thanks for the help, I have always used linked tables so this will be something new. Hopefully with your very clear example I can follow and get this to work.

I appreciate your time and help!

 

[Guest_imported]

hello!

I used to backup up my access mdb file on a 1.44 disk (after uzing winzip) on a daily base.

Now the ZIP file is bigger then 1.44.

Is there any other way for doing it?

10X...

 

[jagilman]

orens1, use the span disk option in winzip. it lets you use more than one disk.
John A. Gilman
gms@uslink.net