Importing and sorting data from one table to another

[chemosabe]

Hi I am pretty green when it comes to ACCESS VBA, ive done some in Excel, but i need some help. I have a data with several records, and these contain data in no particular order.
Some of these data need to be modified before being moved to the other database. The code looks like this:
#
Dim cnn0 As ADODB.Connection 'Reciever DB
Dim cnn1 As ADODB.Connection 'Original DB
Dim Rst As ADODB.Recordset
Dim orgRst As ADODB.Recordset

Set cnn0 = CurrentProject.Connection
strCnn = "provider=Microsoft.Jet.OLEDB.4.0;"
strCnn = strCnn & "Data Source="
strCnn = strCnn & strRecPath
strCnn = strCnn & ";" & "User ID=Admin;"

Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

Set Rst = New ADODB.Recordset
Rst.ActiveConnection = cnn1
Rst.CursorType = adOpenDynamic
Rst.LockType = adLockOptimistic

strSQL = "select * from " & strDBname
Rst.Open strSQL

Set orgRst = New ADODB.Recordset
orgRst.Open "select * from crf11", cnn0, adOpenDynamic, adLockOptimistic Rst.MoveFirst
If Not (Rst.BOF And Rst.EOF) Then
Do Until Rst.EOF

For x = 0 To Rst.Fields.Count - 1

Select Case Rst.Fields(x).Name

Case "Time_Stamp"

strINcrf11 = strINcrf11 & "INSERT INTO crf11(" & _
Rst.Fields(x).Name & ") VALUES " & _
Chr(39) & Rst.Fields(x).Value & Chr(39) & ";"
​

Case "dato"

strINcrf11 = strINcrf11 & "INSERT INTO crf11(" & _ Rst.Fields(x).Name & ") VALUES " & _
Chr(39) & Rst.Fields(x).Value & Chr(39) & ";"
​

' ... this is repeated for some 200 records
​

End Select
​

Next x
orgRst.DoCmd strINcrf11
strINcrf11 = ""
Rst.MoveNext
Loop
​

End If
#
Now, this creates a long string of INSERT INTO crf11(tablename) Value 'xx';INSERT INTO crf11(tablename) Value 'xx'; etc... These values are not in order, and the sql string is very long. The values are boolean, dates, numbers, and strings. What am I doing wrong? With regards Howard

 

[KenHigg]

I think if you put your code in the 'code wrapper' tags it will maintain its formatting and be easier to read

Just hit the '#' sign in the forum text editor and paste it in between the 'code' tags -

 

[chemosabe]

Thanks, ken