I am having some performance issues in my Access 2000 app. My 'procedures' MDb is called 'ProgProc.MDB'. It is stored on the local drive of every user machine. The data MDB is called ProgData.MDB and is stored on the network drive.
I will use 'tblAnswers' which is physically in ProgData.MDB but linked to ProgProc.MDB as an example. This table has an autonumber field called 'AnswerID' as the primary key.
I want to create a complete duplicate of tblAnswers with exactly the same AnswerID's, locally in ProgProc, stored in a table called 'TblAnswers_Local'.
I have tried two different methods of doing this, each had its own drawbacks.
1st Try: I created a macro using the 'TransferDatabase' command. 'tblAnswers' was the source, 'tblAnswers_Local' was the destination. It worked great (almost). It created a complete copy of 'tblAnswers' in 'tblAnswers_Local' with exactly the same AnswerID's. The problem is that it created it as a linked table. The whole point of this excercise is to create a local table to improve speed. This solution is out unless somebody knows a way to change it so that it works the way that I want.
2ndTry: Using 'tblAnswers' I created a local table in ProgProc called 'tblAnswers_Local'. They had exaclty the same structure. Then I created the following routine to copy the contents of one table to another. The routine will work for any two table with the same structure.
Public Sub CopyTable(passedInputTable
As String, passedOutputTable As String)
'
' Input table and output table must have the same structure
'
ClearTable (passedOutputTable) ' routine to delete all of the records in the passed table
'
' Open output table
'
Dim rsTblOut As ADODB.Recordset
Set rsTblOut = New ADODB.Recordset
rsTblOut.Open passedOutputTable, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'
Dim rsTblIn As ADODB.Recordset
Set rsTblIn = New ADODB.Recordset
rsTblIn.Open passedInputTable, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'
Dim i As Long
Dim j As Long
'
If rsTblIn.EOF Then
Exit Sub
Else
If rsTblIn.RecordCount > 0 Then
'
rsTblIn.MoveFirst
While Not rsTblIn.EOF
'
With rsTblOut
.AddNew
' move values from inout to output
For i = 1 To rsTblIn.Fields.Count - 1
For j = 1 To rsTblOut.Fields.Count - 1
If rsTblIn.Fields(i).Name = rsTblOut.Fields(j).Name Then
rsTblOut.Fields(j).Value = rsTblIn.Fields(i).Value
End If
Next j
Next i
.Update
End With
rsTblIn.MoveNext
Wend
End If
End If
'
rsTblIn.Close
Set rsTblIn = Nothing
'
rsTblOut.Close
Set rsTblOut = Nothing
'
End Sub
I thought it was pretty slick and it worked, sort of. The problem was the AnswerID's in 'tblAnswers_Local' were not the same as the answerID's in 'tblAnswers', this is no good either they have to be the same.
I then changed the 'answerID' field in 'tblAnswers_Local' to be a Long Integer Number instead of Autonumber. Then when I did the copy I got key violations. It looked like it was putting zero in every 'AnswerID' in 'tblAnswers_Local'.
Any suggestion are appreciated whether they involve my Try number 1, try number 2 or any alternate ways of accomplishing this.
Start Free Trial
