Comparing field names of two tables

[saranmc]

and the module that contains my GetAppendSQL is as follows,

Option Compare Database

Private Function GetAppendSQL(strFrom As String, strTo As String) As String

Dim strField As String
Dim dbvar As DAO.Database
Dim fldVar As DAO.Field
Dim flsFrom As DAO.Fields, flsTo As DAO.Fields

Set dbvar = CurrentDb()
Set flsFrom = dbvar.TableDefs(strFrom).Fields
Set flsTo = dbvar.TableDefs(strTo).Fields
On Error Resume Next

For Each fldVar In flsFrom
         'Reset each time for test strField = ""
        'This next line will fail if named field not present.
        
strField = flsTo(fldVar.Name).Name
        If strField > "" Then _
            GetAppendSQL = GetAppendSQL _
                           & Replace(", [%N]  L          ", "%N", strField)
     Next fldVar
    If GetAppendSQL = "" Then Exit Function
    GetAppendSQL = Replace("INSERT INTO [%F]%L" _
                    & "          ( %N )%L" _
                    & "SELECT      %N%L" _
                  & "FROM        [%T]" _
                    , "%N", Mid(GetAppendSQL, 3))
    GetAppendSQL = Replace(GetAppendSQL, "%F", strFrom)
    GetAppendSQL = Replace(GetAppendSQL, "%T", strTo)
    GetAppendSQL = Replace(GetAppendSQL, "%L", vbNewLine)

 End Function

I have not changed anything as you told me not to...

[NeoPa]

Hi Saran.

This seems a little weird. You have it defined as Private in a module that's separate from where it's called from. I would expect that to fail completely as it won't see the code at all.

Are you seeing anything in the way of SQL come out of this at all?

If you want the code to be more general, and usable from anywhere in your project, then it needs to be defined as Public rather than Private. Another point to note in your modules is to use Option Explicit universally (See Require Variable Declaration).

If that fails then try updating your button procedure (Just temporarily.) to :

Private Sub Command64_Click()
    Dim strSQL As String

    strSQL = GetAppendSQL(strFrom:="tbl_Import",strTo:="MLE_Table")
    Call MsgBox(Prompt:=strSQL)
End Sub

Run it through and then describe what you see in the message box that comes up.

[saranmc]

Hi NeoPa,

so i made the changes that you said (private to public) and Option explicit. Now the code runs but nothing happens.

and when i tried the new code you gave me. it gives a message box with the SQL as follows,

INSERT INTO [MLE_Table] ([pnr], [datacode], [overallassesment], [techassesment],[risk], [reason], [justification], [comments], [findings], [remarks])
SELECT ([pnr], [datacode], [overallassesment], [techassesment],[risk], [reason], [justification], [comments], [findings], [remarks] 
FROM [tbl_Import]

These are the field names of the target table (MLE_Table). the msgbox appears but the fields are not copied.

The source table(tbl_Impor t) does not have all the fields of MLE_Table so I Need to copy only the fields that match and leave the others blank. so the code has not checked for the matching fields. I think the FOR EACH Loop has not really worked.

[NeoPa]

Hi Saran.

The results you've posted do not match what I would have expected from the code I posted. It should not be possible for a field that is only in one table to appear in the resultant SQL. the SQL should be formatted very differently from what you've posted. All in all, I can't match what you're posting with the code I posted so I suspect you've failed to copy and/or use the code correctly as instructed.

If you like you can post a sanitised version of your database, with both tables converted to local tables if they aren't already, and I will look at what is there and what isn't. That way I will have something reliable to work with and I'll be able to tell you exactly what's missing. It may be there's a problem with my code, but from what you've posted that seems unlikely. Send me a zipped copy and I can tell you for sure though.

NB. Only those two (local) tables should be required, as well as the code of course - any object or module that contains any of the code we've discussed.

[saranmc]

Hi NeoPa,

Thank you for your ideas, I finally managed to solve my Problem with help from some experts.

Incase anyone else has a similar Problem and for anyone who followed this post, here is the code that solved my Problem.

Sub import_function()

Dim qd As New DAO.QueryDef
Dim dbvar As DAO.Database
Dim strSQL As String
Dim m As Integer
Dim n As Integer
Dim mystr As String
Dim str As String
Dim stp As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Set dbvar = CurrentDb()
Set rs = CurrentDb.OpenRecordset("MLE_Table")
Set rs1 = CurrentDb.OpenRecordset("tbl_Import")

With rs
For n = 0 To .Fields.Count - 1
str = CurrentDb().TableDefs("MLE_Table").Fields(n).Name
    With rs1
    For m = 0 To .Fields.Count - 1
    stp = CurrentDb().TableDefs("tbl_Import").Fields(m).Name
        If str = stp Then
            mystr = mystr & stp & ", "
            Exit For
        End If
    Next m
    End With
Next n
.Close
End With
 
 mystr = Left(mystr, Len(mystr) - 2)
 
 strSQL = " INSERT INTO MLE_Table (" & mystr & ")" & _
           " SELECT tbl_Import." & Replace(mystr, ", ", ", tbl_Import.") & " FROM tbl_Import;"

                
 DoCmd.RunSQL strSQL

End Sub

Thanks a lot NeoPa, I will be back if I have further Trouble with my Project.!!

Cheers!!

Saran