[RESOLVED] INSERT INTO without field names

[saranmc]

I am trying to use the INSERT INTO Statement without defining my column field names. I know this is possible but i am jus not able to do it. The SQL string i currently have is,

 strSQL = " INSERT INTO MLE_Table (pnr, [Overall Assesment], risk, reason, justification)" & _
           " SELECT tbl_Import.pnr, tbl_Import.[Overall Assesment], tbl_Import.risk, tbl_Import.reason, tbl_Import.justification " & _
           " FROM tbl_Import;"

now, in this code i have used my field names. but I dont want to do that. I want the SQL to insert fields into the new table only when the field names of both tables match.

I think it can be done by a For Each Loop, but I am not sure.

Is there anyone who has done this before.. I am using MS Access 2010.

Thanks a lot!!

[westconn1]

i found this

Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

[saranmc]

I have tried using the VALUES clause with SELECT but not successfull!!
The code runs but does not copy anything.

[techgnome]

DANGER! DANGER WILL ROBINSON! DANGER!

In short you can't do it... the only way to do it is if the fields selected MATCH EXACTLY ONE FOR ONE IN TYPE AND ORDER... and I do NOT recommend this... it is not a good idea. this is one of those things that jsut because you can does not mean you should or that it is a good idea. Sure, I could shoot myself in the foot. I could. But I'm not going to because it's a bad idea. There really is no excuse for exercising good habits and not listing the column names in the insert. None.

"AND ORDER" ... that's why this is not a good idea... let's say someone drops a field, then adds it back in... it's now no longer in the same spot it was. Let's take the pnr field... someone accidentally drops it from the table, then adds it back in. It's now at the end of hte column order... now your fieldless insert will try to put the tbl_Import.pnr into [Overall Assessment], tbl_Import.[Overall Assesment] into risk, and so on, sifting everything down... IT DOES NOT GO, "oh, hey, there's a prn field in the select clause, so map it to the prn field of the table." ... no, it goes, OK, I have this numeric field in pos 1, hey, look field 1 in the table is a string... let's convert that and stuff it in... next field... oh hey it's a string... and in the table it is.... a boolean... oh crap... throw an exception...


-tg

[saranmc]

@techgnome, the reason that i want to do this is because at a later stage incase i Need to Change the column names i dont have to edit all my codes with the new Name!!
thats why i want to do my codes with column positions rather than the exact names. it is like a dynamic way rather than having static names.
i was thinking of something like a FOR Loop with,
an IF statement to check for the field1.name of the source table in the recordset of the field names of the target table.
But i have not been able to get this idea to work still...

[techgnome]

Honestly... as someone who has been doing this for a long time... and deals with tables that have lots of fields in them, some as few as eight, most are in the 20-50 range, and even a few that reach into the triple digits... trust me... it. is. not. worth. it. Write out those names. It will save you from a world of hurt. It is A HECK OF a lot easier to deal with errors when you list out the cols. Yes, if you change it, you'll have to go into the query and change it there too... you know what happens when a column is renamed? A new temp col is added, the data copied over from the old col to the new col, the old col is dropped and it renames the temp col to the new name... seriously. Remember what I said about the order being important? It just moved ... now your select is yacked because the cols in the target table shifted... If you're explicit with the col names, it will become apparent what needs to be updated real quick - this is a good thing. But using your method, if the cols shift and the types are compatible... you'll never know... and it will just go right ahead and keep inserting data into the wrong columns.

Trust me, you are not saving yourself anything by going down this road. Name the table, name the fields, be explicit each and every time.

-tg

[techgnome]

that said... I'd also highly recommend you change hte field names NOW! and get rid of the spaces... that's another one of those things that just because you can doesn't mean you should. And like the inserts, having spaces in your field (or any object) name is just one more ingredient for disaster down the road.

-tg

[saranmc]

I completely understand what you mean techgnome. And you are right.
But if I am to do that, I am facing another Problem.

Like I said there are two tables, the source and the target.
This source table is a user uploaded table. So the order of the column names cannot be said that they will always be in the same order as the target table (all columns here are fixed in an order).

So in short, the user cannot be trusted to upload his table with column names as the same order as the target table.

So now what do I do?? How can i make sure that the columns are correctly inserted into the target table.

Thanks a lot for your ideas techgnome!!

cheers.

[techgnome]

You do exactly as you were:
INSERT INTO {someTable} ({Field1}, {Field2}...) SELECT {Field1},{Field2}, ... FROM {someotherTable}

Now you DON'T care what order they are in in either table.

-tg

[saranmc]

I have tried that techgnome, it works. But not always.
There is a Situation where this SQL Fails.

a column Name mentioned in the SQL is missing in source table. This can happen as it is a user uploaded table and the column might be missing.
this does not mean that the user has made a mistake!!
in this case, i want the SQL to copy the other columns and leave the missing column blank in the target table.
But the SQL completely Fails. copies nothing!!

This has been a Major Problem for me from the start!!
Is there any way to solve this please??

[westconn1]

use code to build your sql string from the column names in the table

[saranmc]

Could you please be more specific @westconn1...
sorry, my VBA Expertise is not so good...

[westconn1]

to build a string of field names, you can try like

sql = "select * from tbl_Import"
rs.Open sql, cn, adOpenStatic, adLockReadOnly
For f = 0 To rs.Fields.Count - 1
    fldlst = fldlst & "tbl_Import." & rs.Fields(f).Name & ", "
Next
rs.close

the generated string can be concatenated into your insert sql, strip the trailing ", "
you will need additional handling if field names can contain spaces, to put enclose in []
you could use replace to feed the same columns for the target table, of course, while the order of the columns would not matter, the source column names must be an exact match for the target column names, or you could use some sort of a fuzzy lookup to try and match source to target, if no match then omit
it would probably be more efficient not to return a full recordset of data just to get the field names a better option might be to use a schema or similar, you can do this from the connection object using cn.openschema(), plenty of examples online, if the source tables are only small then it would not matter anyway

[saranmc]

@ westconn1, i tried to execute your code with a few modifications,

Private Sub confirm_Click()
Dim str As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(" tbl_Import ", dbOpenDynaset)
str = "select * from tbl_Import"
rs.Open str, cn, adOpenStatic, adLockReadOnly
For f = 0 To rs.Fields.Count - 1
    fldlst = fldlst & "tbl_Import." & rs.Fields(f).Name & ", "
Next
rs.Close
end sub

but i Keep getting an error on the line

rs.Open str, cn, adOpenStatic, adLockReadOnly

error: Method or data member not found.

its with the "rs.open" Syntax..
I have defined rs, i dont know why the error appears.

[saranmc]

after a bit of searching and examples, i have managed to put together another code but again i have some Errors..

Private Sub Command62_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim dbvar As DAO.Database
  Set dbvar = CurrentDb()
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM MLE_Table"
  Set rstInsert = CurrentDb.OpenRecordset(strSQL)
  strSQL = "SELECT * FROM tbl_Import"
  Set rstSource = CurrentDb.OpenRecordset(strSQL)
  
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = " pnr " Then
                'Insert default .Value
                rstInsert.Fields(.Name).Value = 0
              'ElseIf .Name = " pnr " Then
                ' Ignore this field.
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

ERROR: Item not found in this collection

rstInsert.Fields(.Name).Value = .Value

any ideas please??

[techgnome]

That's a case where you have the opposite problem... something is in the source that's not in the taerget...
Check to see if rstInsert.Fields(.Name) is Nothing before setting the .Value of it... only set it if it is not nothing.

-tg

[IkkeEnGij]

if i understood correctly,
then the autonumbers are not used for anything,if that is so, then why are they there ?
what i would like to know: how have you succeeded in creating fieldnames with leading/trailing spaces ?
suggestion:
upload the db here,so we can have a look at it

[westconn1]

the code i posted was based on ADO, if you want to use DAO you need to modify to suit

[saranmc]

@techgnome, rstInsert.Fields(.Name) cannot be nothing. Because i am updating/ inserting into a table. That means I am inserting into a field that has some value. So adding some new records at the end. So can the value of the field be nothing?? I dont think so.. Please correect me if I am wrong...

[saranmc]

@ikkeengij... no there are no autonumbers...

so after some some discussions with some other colleagues and taking their suggestions... i have finally compiled a code, though it is not completely working yet, the logic is I think what most of you guys mean. please have a look at it.

Sub import_function()

Dim qd As New DAO.QueryDef
Dim dbvar As DAO.Database
Dim strSQL As String
Dim stt(10) As String
Dim m As Integer
Dim n As Integer
Dim p As Integer
Dim minSQL As String
Dim PNR As String
Dim ret As Integer
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(n).Name
    
        If str = stp Then
            stt(n) = stp
            Debug.Print stt(n)
            Exit For
        End If
    Next m
    End With
Next n
.Close
End With
 
 
 
    strSQL = " INSERT INTO MLE_Table(stt1, stt(2), stt(3), stt(4), stt(5), stt(6), stt(7), stt(8), stt(9), stt(10))" & _
                    " SELECT stt(1), stt(2), stt(3), stt(4), stt(5), stt(6), stt(7), stt(8), stt(9), stt(10) FROM tbl_Import;"
               
              

DoCmd.RunSQL strSQL
  

End Sub

the code runs... the string values are an Array.. but i cannot assing them to the SQL.....

[westconn1]

you need to append each field name to a string, rather than an array,
or use join with the array something like

strSQL = " INSERT INTO MLE_Table (" & join(stt, ", ") & ")" & _
           " SELECT tbl_Import." & join(stt, ", tbl_Import.") & " FROM tbl_Import;"

make sure to print the strSQL to the immediate window to make sure it is as intended
you would need to make sure your array does not have any empty elements, if the number of fields can vary then you should use a dynamic array and redim preserve it to the number of valid fields found and matched

[techgnome]

@techgnome, rstInsert.Fields(.Name) cannot be nothing. Because i am updating/ inserting into a table. That means I am inserting into a field that has some value. So adding some new records at the end. So can the value of the field be nothing?? I dont think so.. Please correect me if I am wrong...

I said to check to see if the FIELD is nothing, not it's value.... but I can see the confusion.

Here's what I see and no code anywhere in this thread will solve it ... you have two tables... table tblImport and tblTarget ... you have run into a case where tblTarget has a field that tblImport does not ... ok.... so you loop through tblImport, build up a string of fields and insert into tblTarget....the case above is the reverse problem... there is a field in tblImport THAT IS NOT IN tblTarget. Just like you cannot select from a field that doesn't exist, you cannot insert into a field that doesn't exist. That's what the error on this line:
rstInsert.Fields(.Name).Value = .Value
was telling you... you were trying to assign a value to a field that simply put did not exist. It's like asking for an appointment on the 30th of February ... no matter how you put it, that simply cannot be done as the date just doesn't exist.


Personally I think you're working with either a crap requirement, or a crap design... I'm leery of anything that just allows users to arbitrarily upload anything they want with out some kind of standards or a set of rules that have to be played by. I know systems that allow the uploading of data, heck, that's what I'm working on in my day job at the moment, but there are usually some kind of processes and rules that have to be played by... this just sounds way too arbitrary.

-tg

[westconn1]

no code anywhere in this thread will solve it

his code does test for matching fields in the target, before adding to the array

[saranmc]

@techgnome you have perfectly summarised my Problem. The fact is the database i am building has to contain some past data. Now I will define some rules for the user so that in future he/ she will upload correct and well defined data. But unfortunately the past data are not so well defined. Thats why this big issue.
the only part you have misunderstood is that vice versa is also possible.

A field in tblTarget NOT in tblSource AND ALSO a field in tblSource NOT in tblTarget.... both Scenarios are possible.
IMPORTANT: tblTarget is the defined and Standard table, but it is not necessary that all fields have to be filled for a particular record.

I hope this gives a clearer Picture.

As @westconn1 said, my code compares both field names and selects the matching fields.
The SQL is also in correct Format.
it is the inserting of These strings(field names) into the SQL thats giving me Trouble.

@westconn1... If you see my for Loop, I am storing (stt) value as the matching field Name. But when i reuse it in the string it does not recognise.
as you said, "append it to a string"... is that not what i am doing??
i cant make (stt) as a string because an error appears "object not found"
it has to be an Array....

i feel i am really Close... with experts like you guys i think i can do this.... thanks a lot!!!

[saranmc]

OMG!!! westconn1 your code connected the Arrays to the SQL!!!!

You are a Genius!! There is one more error, I hope it is the last one..
I have a Syntax error in the INSERT INTO Statement when i execute the Statement.

i checked out the Statement through a message box and it reads,

INSERT INTO MLE_Table (pnr, , , , risk, reason, justification, , , , )
SELECT tbl_import.pnr, tbl_import., tbl_import., tbl_import., tbl_import.risk, tbl_import.reason, tbl_import.justification,
tbl_import., tbl_import., tbl_import., FROM tbl_Import

The reason for this, you probably already guessed is that there are 10 fields in MLE_Table and the match has occured for four fields.
so the final result should be that those four fields should fill up leaving the other six blank.
i think thats the reason for the Syntax error.

any ideas please on how to make the string accept empty when there is no match??

[IkkeEnGij]

as long as the following is true
1)each field in source does exist in destination
2)correspondenting fields in source and destination are the same datatype
3)correspondenting fields in source and destination have the same constrains
4)the fields in destination do accept null's
the following will succeed

insert into destination
select *
from source

take care of nr2:
if a field of destination is a text field,
and the correspondenting field in source is numeric
then the db will most likely convert the number in a string
to summarise:
if it is at all possible to append source to destination the following will succeed:

insert into destination
select *
from source

[westconn1]

If str = stp Then
stt(n) = stp

this only inserts valid fields into the array, but the counter (n) is still incremated
i would still recommend just using a string, rather than an array, but you could remove empty elements from the array like

strSQL = " INSERT INTO MLE_Table (" & replace(join(stt, ", "),", ,", ",") & ")" & _
           " SELECT tbl_Import." & replace(join(stt, ", tbl_Import."), ", ,", ",") & " FROM tbl_Import;"

you need to use the same array of fields for the select as the target
if i got all the , in the right places, it should remove the empty spaces
but as i only typed this in the browser it may contain typos or other errors
on reconsideration i do not think this will work correctly
better to append to a string

        If str = stp Then
            mystr = mystr & stp & ", "

as per my original example (post #13)
you will need to remove the trailing ", " and insert the appropriate table name where applicable

[IkkeEnGij]

I am using MS Access 2010.

are you really ?
if so, why dont you just use access2010 to create an insert query ?
no programming at all,and access will show you all the fields from source
just drag them to the grid and if there is a corresponding field in destination,access will match them

[techgnome]

Ike - I take it you haven't followed closely enough ... the source table is uploaded by the user... it's not always the same, and it doesn't always have the same fields as the target... Access won't let you build a query against a table that doesn't exist yet... and even if it did, the fields could change. At any given time it could be called tblFoo with one field, "Bar" and the next time it's tblFooBar with two fields, Snafu and ID ...

-tg

[IkkeEnGij]

all that is quite irrelevant if you use access
to import or link an external table you just ask access to do it
now the external table is in access (lets call it 'source')
now you ask access to create a query
access will ask you for what tables
chose source
access will place source in its query generator
ask access to create an insert query
access will ask you wich table you want to insert records to
chose the destination table
dblclick each field in the source
access will place that field in its query generator grid
together with its correspondenting field in destination
if there is no correspondenting field in destination,then there will be no correspondenting field in the query generator grid
and you will know that access will not try to put that field in destination
if you are content ask access to execute the query
or/and
ask access to show the sql it will generate

some caveats
access matches the fields by their name,not by their datatypes..so that could fail
if there is a requered field in destination that is not in source...it will fail
if for correspondenting fields in both tables, the source violates a constraint in destination...it will fail
etc...
but whatever the reason of a failure...access will tell you what and why

so to be secure make a copy of the production database just to try it out
or at least make a copy of destination to try it out

or in an access module create a procedure,and wrap the generated sql in a transaction
if it succeeds...commit
if not ...rollback
an other option is simply to loop trough source
and try to add every record of source to destination
if succes..ok
if failure just write the failed record of source to an empty copy of source
or better,since there are no autonumbers just add an autonumber to source
and create a 'failures' table with just 2 fields
1 long integer field for the failed record's autonumber and a text field for the reason of failure
this unfortunately will need to be rewritten if there are large variations in source

while using the earlier method nothing will have to be rewritten...just a few mouse clicks

[techgnome]

It's very relevant... so your solution is to ask the users to go through all of that every %^$% time they want to import some data? All those steps are exactly what the OP is trying to automate.

-tg

[IkkeEnGij]

you'r right
my solution requeres at max 2 minutes every time it is needed
how many hours has OP already spent trying to find a coded solution ?
even with the help of the participants here ?
seems to me sofar without succes
also dont forget i have already given a very good solution in post#26
together with the requerements for it to work
but hey,i know how satisfying it can be to come up with your own coded solution to a problem that requeres 2 minutes to solve with the right tool
after all thats the joy of programming, right ?

[westconn1]

the problem with the solution in post #26 is the first line, as has be repeated several times, not all are true

i know how satisfying it can be to come up with your own coded solution to a problem

of course, also a learning experience

i can not comment on how well access will do the required task as i do not have access, like most i try to assist with requested solution, not try to force some alternative on the op, suggestions of more fitting methods yes, but i would assume that the op has tried or previously been using access for this task and finding it time consuming, inadequate or other, has decided to look for a better solution,

the code for what the requirement is simple enough, just takes a little experience to make it work reliably

i am glad that i did not do as you suggested, with several of the small automation projects i use at work daily, from both the time saving, satisfaction and experience gained points of view

for some writing code can be a hobby, as good as any other, i do not write code as part of my employment
all have to start somewhere, books, asking friends, etc, were the previous ways to learn, this forum makes it so much easier for many, though some abuse, expecting all the code to be written for them

[IkkeEnGij]

all in all, the solution is very simple
what is the problem...incompatibility between 2 tables
so just remove that incompatibility...how ?
i am the client,i have a table that needs to be appended by a table supplied by you
so i supply you with my empty table,and a program to put records in it
since i supply the program to fill the table,i make sure you can not put garbage in it
so you fill the table with data,send it to me,and i just:
...insert into my_table select * from your_table...
thats all there is to it
since both tables are exactly the same, it does not matter what fields are filled or not
the insert will succeed

after all, i am the client, i pay you for delivering data to me, i call the shots

[westconn1]

after all, i am the client,

maybe the ones sending the data are the client? the client can do as they wish, send however they want

[IkkeEnGij]

quite true
but for sure the clients will be very happy if i hand them a tool that prevents them from making mistakes
that way my clients are assured they recieve the service they asked and deserve

if i can only supply american fighter planes, and the client wants a MIG21,my tool will tell the client that is not possible (not in stock)
if the client insists,the client can always contact me, and i will see what i can do
if i succeed in getting my hands on a MIG21's (does not seem to be that difficult) i will warn the client
and addapt my tool to make it my clients possible to order MIG21's
the client is king , and i will do all what is possible to make the client feel like a king
even if i have to hold my clients hand and gently prevent them to do the impossible

[saranmc]

guys, thank you for a very interesting discussion.... lots of interesting ideas..

it is true that i have spent quite a few hours on this database, but that is not important. what is important is that the user must have minimum difficulty and effort while using this DB. further on there will even be mathematical calculations based on this. so the aim is to automise it as much as possible.

thats why a simple SELECT query would not be sufficient. If it was, I would be very happy. Unfortunately its not.

I am going to try westconn1's code to try to append the values to a string. Lets hope it works!!!

[saranmc]

@westconn1, your first set of code in #27 gives the same error, as you expected.

[saranmc]

i replaced,

If str = stp Then
 stt(n) = stp

with

If str = stp Then
   mystr = mystr & stp & ", "

when i printed the mystr it Shows the exact field names that match in a string. perfect!
But i get a type mismatch with the SQL. I only replaced stt with mystr, like...

strSQL = " INSERT INTO MLE_Table (" & Join(mystr, ", ") & ")" & _
           " SELECT tbl_Import." & Join(mystr, ", tbl_Import.") & " FROM tbl_Import;"

what is type mismatch?? is it between the INSERT and SELECT Statements?

[westconn1]

mystr is a string, not an array, so join is not required and will error
change to

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

again make sure to print out strSQL to make sure it is correct