By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,398 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,398 IT Pros & Developers. It's quick & easy.

Relink just not right....can someone help me please

P: n/a
At first I thought I could just delete the tables and import the
tables. BUT my tables have relationships and I can not delete
a table with relations. So that only deleted the non relational tables
and imported them.
Now I am trying to relink the exported tables.
this is the code I am using
Public Function ReLink() As Boolean
On Error Resume Next
Dim astrTableNames(1000) As String
Dim iintTableNames As Integer
Dim intI As Integer, strTableName As String, strServer As String
Dim rst0 As Recordset
Dim dbs As Database
Set rst0 = CurrentDb.OpenRecordset("tlkpSysInfo")
strServer = Nz(rst0!extLocation)
rst0.Close
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strServer, ,
dbReadOnly)
dbs.TableDefs.Refresh
For intI = 0 To dbs.TableDefs.Count - 1
strTableName = dbs.TableDefs(intI).Name
If Left$(strTableName, 3) = "tbl" Then
iintTableNames = iintTableNames + 1
astrTableNames(iintTableNames) = strTableName
End If
Next intI
Set dbs = Nothing
For intI = 1 To iintTableNames
Call TableDelete(astrTableNames(intI))
Next intI
CurrentDb.TableDefs.Refresh
For intI = 1 To iintTableNames
Call TableLink(strServer, astrTableNames(intI),
astrTableNames(intI))
Next intI
On Error GoTo 0
ReLink = True
End Function
================
Public Function TableLink(strLocation As String, strOldName As String,
strNewName As String) As Boolean
On Error Resume Next
DoCmd.TransferDatabase acLink, "Microsoft Access", strLocation,
acTable, strOldName, strNewName
If Err Then
TableLink = False
Else
TableLink = True
End If
On Error GoTo 0
End Function
===================
Public Function TableDelete(strTableName As String) As Integer
On Error Resume Next
DoCmd.DeleteObject A_TABLE, strTableName
MsgBox "deleting " & strTableName
If Err Then
TableDelete = False
Else
TableDelete = True
End If
On Error GoTo 0
End Function
I thought this would work but at best it will do
is for any existing table

tbltest
tblwork
tbldata

tbltest
tbltest1
tblwork
tblwork1
tbldata
tbldata1

the same as when I tried to delete and import.

can someone tell me how to delete or relink these tables with
relationships without getting incrememtal files?

thank you for any and all help
jerry


Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
sparks <sp****@here.com> wrote in message news:<t7********************************@4ax.com>. ..
At first I thought I could just delete the tables and import the
tables. BUT my tables have relationships and I can not delete
a table with relations. So that only deleted the non relational tables
and imported them.
Now I am trying to relink the exported tables.
this is the code I am using
Public Function ReLink() As Boolean
On Error Resume Next
Dim astrTableNames(1000) As String
Dim iintTableNames As Integer
Dim intI As Integer, strTableName As String, strServer As String
Dim rst0 As Recordset
Dim dbs As Database
Set rst0 = CurrentDb.OpenRecordset("tlkpSysInfo")
strServer = Nz(rst0!extLocation)
rst0.Close
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strServer, ,
dbReadOnly)
dbs.TableDefs.Refresh
For intI = 0 To dbs.TableDefs.Count - 1
strTableName = dbs.TableDefs(intI).Name
If Left$(strTableName, 3) = "tbl" Then
iintTableNames = iintTableNames + 1
astrTableNames(iintTableNames) = strTableName
End If
Next intI
Set dbs = Nothing
For intI = 1 To iintTableNames
Call TableDelete(astrTableNames(intI))
Next intI
CurrentDb.TableDefs.Refresh
For intI = 1 To iintTableNames
Call TableLink(strServer, astrTableNames(intI),
astrTableNames(intI))
Next intI
On Error GoTo 0
ReLink = True
End Function
================
Public Function TableLink(strLocation As String, strOldName As String,
strNewName As String) As Boolean
On Error Resume Next
DoCmd.TransferDatabase acLink, "Microsoft Access", strLocation,
acTable, strOldName, strNewName
If Err Then
TableLink = False
Else
TableLink = True
End If
On Error GoTo 0
End Function
===================
Public Function TableDelete(strTableName As String) As Integer
On Error Resume Next
DoCmd.DeleteObject A_TABLE, strTableName
MsgBox "deleting " & strTableName
If Err Then
TableDelete = False
Else
TableDelete = True
End If
On Error GoTo 0
End Function
I thought this would work but at best it will do
is for any existing table

tbltest
tblwork
tbldata

tbltest
tbltest1
tblwork
tblwork1
tbldata
tbldata1

the same as when I tried to delete and import.

can someone tell me how to delete or relink these tables with
relationships without getting incrememtal files?

thank you for any and all help
jerry


That's a lot of code, and I haven't reviewed it all in any detail, but
at first glance, it *might* be the way you're deleting the existing
linked tables. When you delete a table, the TableDefs collection is
re-indexed, so when you delete TableDefs(0), the table that used to be
TableDefs(1) becomes TableDefs(0). If you then delete TableDefs(1),
you're deleting the table that used to be TableDefs(2) - the former
TableDefs(1), now TableDefs(0), never gets deleted.

One way around this is to loop backwards ...

For tdf = db.TableDefs.Count - 1 to 0 Step - 1

--
Brendan Reynolds
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.