Comparing field names of two tables

[saranmc]

I have spent a few hours searching for a similar Problem to mine. though there are a few replies, i could not find any to the Point.

so basically, i have two tables. one is a Standard Access table (table1) and the other is a table imported from Excel (table2). now table2 has the field names of table 1 and also a few other extra fields that i dont Need. now i want Access to compare These two tables and copy into table 1 the contents of whichever fields that it can match from table 2.

i have tried INFORMATION_SCHEMA.COLUMNS, but the code does not seem to recognise this Expression.

can anyone suggest some ideas please!!! can give more Details if required.

Thanks a lot..

saran

[NeoPa]

To copy data from [FromTable] into [ToTable] but only where the fieldnames match you can build up a SQL string in your code which you can then execute.

The basic template of the SQL string for an APPEND query is :

INSERT INTO [FromTable]
          ( [Field1]
          , [Field2]
          , ...
          , [FieldN] )
SELECT      [Field1]
          , [Field2]
          , ...
          , [FieldN]
FROM        [ToTable]

Notice that [Field1] to [FieldN] is common for both the INSERT INTO and SELECT clauses.

This part of the overall string can be built up from comparing the values of CurrentDb().TableDefs("ToTable").Fields().Name with CurrentDb().TableDefs("FromTable").Fields().Name. Where a fieldname from [ToTable] matches one from [fromTable] then it gets added to the string (with the brackets and the comma). Any that aren't in both tables are ignored.

Once the SQL string is fully and correctly formulated then execute it.

[saranmc]

Thank you for your reply @NeoPa. I formulated the append query but there is an issue.

table1 = MLE_Table
table2 = tbl_Import

now, table1 is a Standard table like a master table. table2 changes everytime the user Uploads a new file. so the Information from table 2 has to go into table1 and table1 keeps growing. now the Problem is that table2 is user uploaded and therefore it can be that sometimes a field might be missing in case of this Scenario the code must copy the other fields and leave this field blank in table1. this is where the INSERT INTO Statement does not seem to be working. if one of the fields is missing, then the whole SQL does not work.

thats why i think i Need to compare the field names everytime a new file is uploaded. please help me with this. this is the code that i am using

strSQL = INSERT INTO MLE_Table ( pnr, risk, reason, justification )
SELECT tbl_Import.pnr, tbl_Import.risk, tbl_Import.reason, tbl_Import.justification
FROM tbl_Import;
DoCmd.RunSQL strSQL

[NeoPa]

There are a couple of problems with this.

1. The code you've posted has never compiled in VBA. It's just totally invalid. This is important.
   See Before Posting (VBA or SQL) Code. If I'm to help you at all you need to make sure you follow those instructions. I don't have time to point out all the problems the compiler can already tell you about. I will if you struggle even with what it tells you, but you must do those basics first at least.
2. Please notice that the code I posted in my earlier offering was SQL code. This is also important.
   The instructions were to build up the string using the field names of the fields from both tables and then execute it. There is nothing in your VBA code that indicates you've understood that, so I'll try to make the process a little clearer.

INSERT INTO [FromTable]
          ( [Field1]
          , [Field2]
          , ...
          , [FieldN] )
SELECT      [Field1]
          , [Field2]
          , ...
          , [FieldN]
FROM        [ToTable]

That's the same SQL string, but the variable bits that need adding in the loop are underlined. Notice again that both bits are the same so only need to be worked out once even if they're added to the eventual string twice.

Here's some code that will return a string of the SQL you'll need, including all your field names. This is in the form of a function procedure so is fully portable.

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

[saranmc]

I have made the changes according to your suggestions. here is the code.

INSERT INTO [MLE_Table]
2.           ( [pnr]
3.           , [Overall Assesment]
4.           , [Tech Assesment]
5.           , [Risk]
6.           , [Justification]
7.           , [Reason]
8.           , [Comments]
9.           , [Findings]
10.          , [Remarks] )
11. SELECT     [pnr]
12.           , [Overall Assesment]
13.           , [Tech Assesment]
14.           , [Risk]
15.           , [Justification]
16.           , [Reason]
17.           , [Comments]
18.           , [Findings]
19.          , [Remarks] 
20. FROM        [tbl_Import]
 

So, this will be my Append Query in the second set of codes that you sent??
I have modified that code to my variable names also. But I do not understand "%N", "%L" & "%T".
could you please tell me what exactly they stand for and should i Change them too?

Thanks a lot!!

[NeoPa]

You've lost me. Where does that SQL come from? Is it the returned value of GetAppendSQL()? If not then I'm thoroughly confused. How was it created?

I'm happy to explain everything, but I need to understand where you are and what you already understand or explaining won't work very well.

[saranmc]

No, it is your code. Only I have replaced the field names with my field names.

I have not executed that SQL. I do not know how to. How do i build up GetAppendSQL()?

Please explain step by step. Right now I just have two tables, nothing more. No definite code.
Thanks a lot for your time.

[zmbd]

Easiest, is to link to the excel table,
Create a simple select query against the linked table that shows only the fields of interest and use that query in the remaining application. Unless one is moving from Excel to a database, the hassle of importing the data into a table is often more than it's worth.

Next, I would attempt to do this using the simple external data import and mapping the proper fields, omitting those that are not needed, saving the import for latter use as needed. The import wizard does not do too bad a job at this; however, there's no data validation so you have to make sure that the incoming data from the excel sheet is the proper data type or the record may not import properly

finally, based on the aforementioned linked table, once again, create the select query, then convert to and append query (INSERT) within the Query editor. Switch to SQL view to get the string if needed and go from there... this is where NeoPa is at with the generalized SQL.

[NeoPa]

Hi Saran.

Sorry for the delay responding. It's been hectic here.

My post #4 explains exactly what to do to build up your SQL. In case that's too complicated though, I also included the VBA code that both illustrates the concepts as well as successfully returning the exact SQL you need according to your explanation.

In the VBA code I used % and a letter to indicate those parts of the string that are to be replaced with a specified value. Lines #21 through #28 all use the Replace() function.

The SQL code you posted appears to be in the right format as far as I can see. Considering your line #4 of the code you posted in post #3 shows a valid way to execute a SQL string in VBA I'm not sure what more you need.

[saranmc]

Hi NeoPa, Hi zmbd,

thanks for confirming that my SQL is in the right Format.

The issue that I am having with the SQL (append query) is that incase(for eg.) table2.field2 and table2.field5 are empty then this INSERT INTO Statement does not work. It does not copy the other fields into table1. the code runs but all the fields in table1 are empty!!

Now as i understand NeoPa, i have to generate this SQL code using your #4 code. then maybe i will not have this problem. so i am going to try that.

Im a Little confused with where to use your code. should i make a new module and enter this code into a private property.? and i run it to produce the SQL in the intermediate window?

sorry for my lack of VBA Expertise. thanks a lot for the help!!

saran

[saranmc]

NeoPa could you please explain your code to generate the SQL a Little more in Detail. I am not sure where to replace my variable names in your code.

The REPLACE Statement is giving me some Trouble.

strFrom (from table) = tbl_Import
strTo (to table) = MLE_Table

my field names in strFrom are,
[pnr],[technical assesment],[risk],[reason],[justification],[comments]

my field names in strTo are the same but includes more fields of random names and also sometimes the fields in strFrom might not be present.

As i understood the places where you have used [%alphabet] are to be replaced with field names. correct?? But please help me with what appears where.

Thanks!!

[NeoPa]

1. Take the VBA code from post #4 and copy/paste it into a standard module in your project.
   No changes or replacements are needed to this code which should work exactly as pasted.
2. Call the routine and execute the SQL with code like :
   

   Dim strSQL As String
   Dim dbCurDB As DAO.Database
    
   Set dbCurDB = CurrentDb()
   strSQL = GetAppendSQL(strFrom:="tbl_Import", strTo:="MLE_Table")
   Call dbCurDB.Execute(strSQL, dbFailOnError)

To test it in the Immediate pane simply say :

Call dbCurDB.Execute(GetAppendSQL("tbl_Import","MLE_Table"), dbFailOnError)

I can't test this properly as I've typed it all from scratch so let us know if it works. If it doesn't then please explain clearly what does happen.

[saranmc]

Hi NeoPa,

I tried to execute your code, like you said i pasted the code to a new module and i called this module in my form using your second set of code.

I got an error message, on line 5 of your execute code.

strSQL = GetAppendSQL(strFrom:="tbl_Import", strTo:="MLE_Table")

error: "wrong number of arguments or invalid property assignment"
there is something wrong with "GetAppendSQL"

[NeoPa]

That doesn't match the procedure I posted. Or should I say the parameters do match what I posted so I don't see how that error could occur.

Can you paste in your module that contains the GetAppendSQL() procedure please.

[saranmc]

i created a button on my form and used it to call the Private Function GetAppendSQL()

Private Sub Command64_Click()
 
Dim strSQL As String
Dim dbCurDB As DAO.Database
 
Set dbCurDB = CurrentDb()
strSQL = GetAppendSQL(strFrom:="tbl_Import",strTo:="MLE_Table")
 
Call dbCurDB.Execute(strSQL, dbFailOnError)
 
End Sub

the Private function GetAppendSQL() lies in a module. Could that be a mistake??

Thanks NeoPa!!

[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_Import) 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