[RESOLVED] INSERT INTO without field names

[alex_read]

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

[saranmc]

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?

[saranmc]

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

[westconn1]

how does the strSQL print out?

[saranmc]

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 " , " ???

[westconn1]

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

try again

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

[saranmc]

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....

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!!!!!

[saranmc]

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

i think thats the Problem..... ?

[westconn1]

should be

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

[saranmc]

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

[IkkeEnGij]

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

[saranmc]

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.......

[IkkeEnGij]

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

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

[saranmc]

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....