ADO time limit bug: DB connecting to itself

[Guest]

Dear all,

I would be grateful for any help with this issue. I have written the code
below which is executed by a button on a form in an Access 2000 database in
windows 2000. The idea is programmatically to copy a table called tblPubs (a
table of newspapers) into a local database shell in order to allow new
publications to show up, and essentially retrieve the most up to date version
of the database for the remote user (i.e. a remote database "synchs" with a
master database in a different office, but through a dial-up connection).

The problem is extraordinary. I am using ADO (which is relatively new to
me). I simply want to delete existing local tblPubs and copy remote tblPubs
into this database. I am using the SELECT INTO sql statement below, and it
creates the table OK, but when I set the primary key / or simply put an index
on a column (using in all cases the Command object), it *only works from time
to time*. The crucial factors seems to be that
a) it always works if I step through the code step by step SLOWLY
b) it occasionally works if I wait a few seconds before adding the index /
primary key.

Am I right in thinking that MS Access 2000 is taking time to refresh the ADO
connection to itself and that I would have to put all of this code into a
new, third file which simply executes instructions on otherwise unopened
databases? Or, is there a way to "refresh" the CurrentConnection object?
The error message, by the way, is simply that it cannot "find" the new
tblPubs, and hence it is unablee to create an index / primary key in it.

The code is pasted below as two functions: one to copy the table and one to
create the index.

I dearly hope that someone has come across this before and is able to help
me! Many thanks,
Gwyn
--
Gwyn Evans
Database Developer

Private Sub btnGetLatestTables_Click()
On Error GoTo Err_btnGetLatestTables_Click
'Gets the latest tblPubs
Dim cnxRemoteMedia As ADODB.Connection
Dim cnxLocalMedia As ADODB.Connection
Dim strDbLocal As String
Dim strDbMaster As String
Dim strCnxBase As String
Dim rstMaster As ADODB.Recordset
Dim objCom As ADODB.Command
Dim objLocalCommand As ADODB.Command


strCnxBase = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strDbMaster = "\\srv3\Databases\New Media And Press Cuttings\MediaTest.mdb"
strDbLocal = "c:\Gwynevans\New Media And Press Cuttings\SarahGodley_V3.mdb"

Set cnxLocalMedia = CurrentProject.Connection

Set cnxRemoteMedia = New ADODB.Connection
cnxRemoteMedia.Open strCnxBase & strDbMaster

Set objCom = New ADODB.Command
With objCom
.ActiveConnection = cnxRemoteMedia
End With

Set objLocalCommand = New ADODB.Command

With objLocalCommand
.ActiveConnection = cnxLocalMedia
.CommandText = "DROP TABLE tblPubs"
.CommandType = adCmdText

.Execute
End With

With objCom
.CommandText = "SELECT * INTO tblPubs IN " & Chr(34) & strDbLocal &
Chr(34) & "FROM tblPubs"
.CommandType = adCmdText
.Execute
End With

Set objLocalCommand = Nothing
cnxLocalMedia.Close
Set cnxLocalMedia = Nothing
'Set rstMaster = New ADODB.Recordset
' rstMaster.Open "tblPubs", cnxRemoteMedia, adOpenForwardOnly,
adLockReadOnly, adCmdTable
' rstMaster.MoveFirst
' MsgBox rstMaster!PubID & ", " & rstMaster!Publication


Set objCom = Nothing

cnxRemoteMedia.Close

Set cnxRemoteMedia = Nothing

MsgBox "I have replaced the Publications table", vbInformation, "Remote
Synched"





Exit_btnGetLatestTables_Click:
Exit Sub

Err_btnGetLatestTables_Click:
MsgBox Err.Description
Resume Exit_btnGetLatestTables_Click
End Sub

Private Sub btnRecreateIndex_Click()
DoCmd.Hourglass True
Me.TimerInterval = 3 * 1000 ' 3 secs
DoCmd.Hourglass False
Dim cnxDoIndex As ADODB.Connection
Dim objLocalIndex As ADODB.Command
'Now re-open it to create the index!
Set cnxDoIndex = CurrentProject.Connection
Set objLocalIndex = New ADODB.Command

With objLocalIndex
.ActiveConnection = cnxDoIndex
.CommandText = "ALTER TABLE tblPubs ADD PRIMARY KEY (PubID)"
'.CommandText = "CREATE INDEX PubIndex ON tblPubs (PubID)"
.CommandType = adCmdText
.Execute
End With
cnxDoIndex.Close
Set cnxDoIndex = Nothing
MsgBox "Index Recreated", vbInformation, "Synch complete"
End Sub

 

[Cor Ligthert]

Gwyn,

Not to sent you away from this newsgroup.

As you wrote are you using Ado, what is not AdoNet however sometimes as well
called AdoDb.

For that is another newsgroup probably a better place.

Microsoft.public.data.ado

When you look in it, than you will see that the problems are all the same as
yours.

Not an answer however I hope this helps,

(Especially in the weekends it is better to get the right newsgroups.)


Cor