Re: Update a 2nd Table

Well, this is hastily cobbled together code. As such it may not be
efficient and it may have errors. Obviously, it does more than you need
(such as delete and create the tables). But on my machine it creates
the Table that you describe.
Of course, as you specify that you are using Access 2003, it uses ADO.
No doubt, the champions of DAO, (almost everyone), will create and
post entirely different modules (using DAO only) that will do the job
much more quickly and in many fewer lines of code (even allowing for
the removing of table deletion, table creation and the populating of
the original table). Then you will really have SOMETHING!
My advice is to try running the code as it is (assuming you do not have
tables named FirstTable or SecondTable), and, if it works, examine
SecondTable to see if it is what you want. The code is probably
self-explanatory but you could ask any question you want about it.

Public Sub Seth()
Dim r(2) As ADODB.Recordset
Dim a As Variant
Dim z As Long
a = Array(0, 1)
For z = 0 To UBound(r)
Set r(z) = New ADODB.Recordset
r(z).ActiveConnection = CurrentProject.AccessConnection
Next z
With CurrentProject.AccessConnection
On Error Resume Next
.Execute "DROP Table FirstTable"
.Execute "DROP Table SecondTable"
On Error GoTo 0
.Execute "CREATE TABLE FirstTable (Delivery INTEGER, Product
TEXT (10))"
.Execute "CREATE TABLE SecondTable (Delivery INTEGER)"
For z = 1 To 15
.Execute "ALTER TABLE SecondTable ADD COLUMN Product" &
CStr(z) & " TEXT (10)"
Next z
End With
With r(0)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM SecondTable WHERE False"
End With

With r(1)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM FirstTable WHERE False"
.AddNew a, Array(101, "A")
.AddNew a, Array(101, "B")
.AddNew a, Array(101, "C")
.AddNew a, Array(102, "A")
.AddNew a, Array(102, "E")
.AddNew a, Array(103, "C")
.AddNew a, Array(104, "A")
.AddNew a, Array(104, "E")
.AddNew a, Array(104, "C")
.UpdateBatch
.Close
.Open "SELECT DISTINCT Delivery FROM FirstTable"
While Not .EOF
r(0).AddNew Array(0), .Collect(0)
With r(2)
If .State = adStateOpen Then .Close
.Open "SELECT Product FROM FirstTable WHERE Delivery =
" & r(1).Collect(0)
While Not .EOF
r(0).Update .AbsolutePosition, .Collect(0)
.MoveNext
Wend
End With
.MoveNext
Wend
End With
r(0).UpdateBatch
End Sub

.

Relevant Pages

  • Re: Member or Data Member not Found
    ... Microsoft introduced a new data access method in the late 90s known as ADO. ... Access 97 and previous only had DAO in them. ... You must disambiguate as Dim rst As DAO.Recordset. ...
    (microsoft.public.access.formscoding)
  • Re: Need help with a DAO to ADO conversion
    ... the open/close code of the DAO object. ... Dim wksCurr As DAO.Workspace ... Dim fldCurr As DAO.Field ... Set dbCurr = OpenDatabase ...
    (microsoft.public.access.formscoding)
  • Re: Refreshing subform
    ... the new data is added to the subform. ... I do not want to use DAO. ... >> Dim CurrConn As New ADODB.Connection ... >> Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Address List
    ... DAO, so the lines that Duane has for ADO need to be commented out and the ... Dim rs As DAO.Recordset ... Dim strConcat As String 'build return string ... "John Spencer" wrote: ...
    (microsoft.public.access.queries)
  • Re: Moving ADO routine back to DAO
    ... Problems with missing DAO 3.6 references are not unheard of, ... non-trivial app, though. ... > I have a client running an app I developed ... > 110: Dim errloop As Error ...
    (microsoft.public.access.modulesdaovba)