[RESOLVED] INSERT INTO without field names

Here's my 2 cents if I were approaching this problem.

• Temporarily store the destination table field names
• Temporarily store the source table field names
• Perform a lookup & marking/store the matching fields (instr, nested loops etc.)
• Copy the source table into a destination_Staging table
• Perform any extra data manipulation, your maths calculations on the data etc. within this table
• Look to write to a log table if you've many client files to process at once. I.e. info such as "at x date and y time, customer a's data was uploaded. It didn't have source columns b and c that the destination table has. Of 1000 rows, 1000 were successfully copied." (for either you to fix, or shoot back to the client to help fix before another try).
• Depending on the results, decide either to make a copy of this table to later come back to, or then perform the movement (copy then delete) of data from the staging destination_Staging table to the destination table

It could be that the INSERT INTO Statement recognises " mystr " as one Long string and not as different field names.

Maybe this is the reason?

ok now the type mismatch is gone...
But the sntax error in INSERT INTO Statement is back!!

how does the strSQL print out?

I think the Problem is like you said with the trailing " , "....
when i check the SQL there is at the end of the INSERT INTO one more " , " which is not needed....

this could be the cause... how do i remove that extra " , " ???

i forgot about that, even though i had pointed it out before

try again

Code:

---

strSQL = " INSERT INTO MLE_Table (" & left(mystr, len(mystr) - 2) & ")" & _
          " SELECT tbl_Import." & Replace(left(mystr, len(mystr) - 2), ", ", ", tbl_Import.") & " FROM tbl_Import;"

---

yes I just figured it out just before your post....

The code works!!!!! it copies perfectly...
Now i jus did some tests uploading diffirent types of files.... and I realised something....

I think the FOR Loop Need a Little more perfection because....
the match occurs ONLY when both the fields names are in the same Position on the table...

For example: If ' risk ' is the 2nd field in MLE_Table and the 3rd field in tbl_Import, it still Shows up as the field is NOT FOUND....
so there is something slightly wrong with the Loop... dont you think so??

this is my cleaned up Loop....

Code:

---

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(n).Name
        If str = stp Then
            mystr = mystr & stp & ", "
            Exit For
        End If
    Next m
    End With
Next n
.Close
End With

---

I think there should be something missing here.... that i am not doing correctly....
Thanks a lot westconn1!!!!!

so from my understanding... the second FOR Loop
stops when m = n and does not search further.....

i think thats the Problem..... ?

should be

Code:

---

stp = CurrentDb().TableDefs("tbl_Import").Fields(m).Name

---

aaah.... ofcourse so silly of me....
sometimes it is the smallest Detail that we miss!!!!

so finally i can mark this Problem solved....
this Forum has really been helpful...
so i move on with my Project and i will be back if I have more Trouble...

thanks a lot to all the brilliant minds who posted your valuable ideas...
Special thanks westconn1!!!!!!
you are my saviour and a Genius!!!
continue the good work!!!!

cheers!!!!

Saran

hurray, problem solved
really ?
are you just going to ignore your clients, if they order something you normally do not sell ?
way to go...

my dear Ikke...... nowhere in this thread have i mentioned anything about clients or selling anything....
this is a DB of aircraft spare parts and used to record data and carry out some mathematical calculations.
what i just solved is just a small part of a very big project....
and all those who replied to this thread just helped me do exactly what i wanted to, to tmove forward....

thanks for your concern anyways.......

are you really saying that if someone sends you a table with content about airplane parts
and it has a field(s) that does not fit in your "defined and Standard table" ... you just ignore it ?
i would advice you reread post#41 by alex
wonder what the NTSB has to say about that
maybe you could contact them

Quote:

---

National Transportation Safety Board
490 L'Enfant Plaza East, S.W.
Washington, DC 20594

---

Again..... I never said anyone is sending me files with aircraft parts...
I dont want to explain or detail about the files....
Problem solved... Thats all i needed....