CODEWITHSUNDEEP
sqlms-accessvba
Adam Blunt
Adam Blunt

Reputation: 33

In Access VBA, Find field name, insert column into table

I am trying to create a new table where the field name in the source file is a variable. For example, the ID field in the source data could be "ID" or "HB_REF_NO", Date of birth may be DoB, Date of Birth or Date_of_birth

I've made a code that searches for various field names and returns the column they are in but am struggling to transfer the data from those columns into a table

Here is the code, please excuse its no doubt brutish methodology...

Private Sub cmdCompare_Click()

Set db = CurrentDb()
Set RecordSet1 = db.OpenRecordset("OriginalData")
Dim Fld As DAO.Field
Dim FldArray() As String
Dim i As Integer
Dim j As Integer
Dim SQLCreate As String
Dim SQLInsert As String
Dim s As Integer
Dim d As Integer
Dim b As Integer
Dim p As Integer

j = RecordSet1.Fields.Count - 1
ReDim FldArray(j)

'Assigns field names to the array
For Each Fld In RecordSet1.Fields
    FldArray(i) = Fld.Name
    i = i + 1
Next

For i = 0 To j
    If FldArray(i) = "Surname" Then
        s = i
    Else
    End If
Next

For i = 0 To j
    If FldArray(i) = "HB_REF_NO" Then
        d = i
    Else
    End If
Next

For i = 0 To j
    If FldArray(i) = "NC_DATE_OF_BIRTH" Then
        b = i
    Else
    End If
Next

For i = 0 To j
    If FldArray(i) = "POSTCODE" Then
        p = i
    End If
Next


SQLCreate = "CREATE TABLE OriginalComp" & _
        "(ID varchar(255), Surname varchar(255), DoB varchar(255), Postcode varchar(255))"

DoCmd.RunSQL SQLCreate

SQLInsert = "INSERT INTO OriginalComp (ID, Surname, DoB, Postcode) " & _
      "VALUES ('" & FldArray(d) & "','" & FldArray(s) & "','" & FldArray(b) & "','" & FldArray(p) & "');"

DoCmd.RunSQL SQLInsert

End Sub

Upvotes: 0

Views: 2038

Answers (2)

HansUp
HansUp

Reputation: 97131

It would be simpler to examine the source table's TableDef.Fields collection and find the current names for each of your variable field names. You don't need to open the table as a Recordset and you don't need to use an array.

This example will find and use a birth date field whose name matches either *Dob* or *Date*Birth*. (If more than one field in the table could match those patterns, you'll need to substitute patterns which are more selective.)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strDateOfBirth As String
Dim strInsert As String

Set db = CurrentDb
Set tdf = db.TableDefs("OriginalData")
For Each fld In tdf.Fields
    With fld
        If .Name Like "*DoB*" Or .Name Like "*Date*birth*" Then
            strDateOfBirth = .Name
        End If
    End With
Next

If Len(strDateOfBirth) > 0 Then
    strInsert = "INSERT INTO OriginalComp (ID, Surname, DoB, Postcode) " & _
        "SELECT HB_REF_NO, Surname, [" & strDateOfBirth & "], POSTCODE " & _
        "FROM OriginalData;"
    Debug.Print strInsert '<- inspect this in Immediate window; Ctrl+g will take you there
    db.Execute strInsert, dbFailOnError
Else
    MsgBox "Birth date field not found!"
End If

Extend that example to deal with the HB_REF_NO vs. ID field.

Upvotes: 2

JCollerton
JCollerton

Reputation: 3327

The problem is here:

SQLInsert = "INSERT INTO OriginalComp (ID, Surname, DoB, Postcode) " & _
      "VALUES ('" & FldArray(d) & "','" & FldArray(s) & "','" & FldArray(b) & "','" & FldArray(p) & "');"

The FldArray variable is a list of all of the column names, so the dth entry will be the dth column name, which is why you are inserting column names instead of values!

If you want to insert into OriginalComp those values then why not do:

Private Sub cmdCompare_Click()

Set db = CurrentDb()

SQLCreate = "CREATE TABLE OriginalComp" & _
            "(ID varchar(255), Surname varchar(255), DoB varchar(255), Postcode varchar(255))"

DoCmd.RunSQL SQLCreate

SQLInsert = "INSERT INTO OriginalComp (ID, Surname, DoB, Postcode) " & _
            "SELECT HB_REF_NO, Surname, NC_DATE_OF_BIRTH, POSTCODE " & _
            "FROM OriginalData"

DoCmd.RunSQL SQLInsert

End Sub

Upvotes: 1

Related Questions