Somebody PLEASE Help!!!! 1

[Cleis]

Hi Group:

I've been messing with this for WAY too long! All I want to do is copy records from one table to another using code.
I would like to copy records from tblContact to tblNew. The code below will also use a active X progress meter. Sombody PLEASE HELP, I'm sick of playing with this!!!!!

This is what I have so far:

Dim dbs As Database
Dim stContacts As Recordset
Dim intRet As Integer
Dim rstNew As Recordset
Dim tblNew As Recordset
Dim intCount As Integer
Dim strQuery As String
Dim sngPercent As Single
Dim varReturn As Variant
Dim lngEmpID() As Long
Dim CountRecords As Variant


On Error GoTo ErrorHandler

strQuery = "SELECT tblContact.* FROM tblContact;"



intCount = 0
Set dbs = CurrentDb
Set rstContacts = dbs.OpenRecordset(strQuery, dbOpenDynaset)
Set rstNew = tblNew
CountRecords = DCount("[Record]", "tblContact&quot
prgTest.BarStyle = tacmPrgSolid
prgTest.Max = CountRecords
Me.Start = Now()
DoEvents

With rstContacts
If .EOF Then ' If no records, exit.
Exit Function
Else

intRet = prgTest

End If

Do Until .EOF

If !Record >= 1 Then
.AddNew
****What goes here???????????????
.Update

End If

If .PercentPosition <> 0 Then
intCount = intCount + 1
prgTest.Value = intCount

End If

.MoveNext

Loop

.Close
Me.End = Now()
Me.prgTest.Visible = False


End With

ErrorHandler:
Select Case Err
Case 0
Exit Function
Case Else
MsgBox &quot;Error &quot; & Err & &quot;: &quot; & Error, vbOKOnly, &quot;ERROR&quot;

Exit Function

End Select

End Function

 

[rhinoman]

Hi,

you have not done a couple of things correctly, so here goes - - -
** this is OK Set rstContacts = dbs.OpenRecordset(strQuery, dbOpenDynaset)
** you need to open the tblNew like
** set tblnew =dbs.openrec..., get rid of rstNew altogether

With rstContacts
... other stuff left out for brevity
Do Until .EOF

*** what is this for? If !Record >= 1 Then
*** you need to add to tblNew so change to read
*** tblnew.addnew
*** tblnew!<fieldname> = rscontacts!<samefield>
*** and update tblnew.update
End If

That should be it Methinks?

 

[MichaelRed]

Can we pause for a moment? The &quot;code&quot; is replete with minor errors, however the larger err appears to be even approaching the process with code.

Perhaps, you have some specific REASON for using code, however is is not evident (to me) in what is posted.

I would approach this as a simple append query.

The below is a SAMPLE append query. You can use the Query Builder (Query By Grid) tool to build one for your [tblContacts] and [tblNew], and add conditional clauses as necessary. Just rember that if your tblNew cannot include an AutoNumber field from tblContacts as an autonumber field, but you can copy the autonumber field to a plain old LONG type field.





INSERT INTO tblRaceTime ( MyIndex, RaceTime )
SELECT RaceTime_2.MyIndex, RaceTime_2.RaceTime
FROM RaceTime_2;
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[rhinoman]

If the number of records being copied is large then the code method put forward seems Ok if you want feedback on process. A small amount of records certainly use an update query

 

[tlbroadbent]

I agree with MichaelRed that a simple append query would certainly seem more appropriate. Doing a large number of inserts in a VB code loop is extremely inefficient. Jet is very efficient at table and set handling. If an append query runs a long time, you can bet that doing the same adds in VB code will increase the time greatly.

If the only purpose of doing this process in code, as I suppose, is to provide visual feedback, why not let Access do it with its progress bar? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[mac318]

I also agree with MichaelRed. If the database is not on a network, Michael's SQL will run so fast that you will only need a &quot;process complete&quot; message to let the user know that some actually happened.

mac318

 

[Cleis]

Thanks Rhinoman! I was able to figure this all out and my end result works great! The reason I wanted to use the progress meter even though it is not as efficient as a regular append query is this . . . My network at work is EXTREAMLY SLOW!!!!!! Even though we are 100klbs a sec, it's still slower than @#$%#. I'm only importing 2,000 records at a time so the net effect between a normal query and the code above is nominal. I just thought under these conditions, it would be better to use a progress meter at the expense of a second or two to let my end users know just where the process was!