Sql "Insert Into"

[igkuk7]

Hi,

I am trying to move data from one very messy database to another. I created a query in the messy database that grabbed values i'd need them imported into the new database. I now wanna transfer the values from the query to the new table I've made.

I figured I would set up a loop in VBA that loops through the query and then inserts each record into the table i need. I'm doing it this way as I am much more comfortable with SQL and code than access interfaces. What I've got looks like this:

Set rs = CurrentDb.OpenRecordset(sql)
    
    While Not rs.EOF
       sql = "INSERT INTO Referrer (referrerID, name, street, town, " _
            & "county, postcode, telephone, fax, email) VALUES " _
            & "('', '" & rs("Ref Name") & "', " _
            & "'" & rs("Ref Address1") & "', " _
            & "'" & rs("Ref Address2") & "', " _
            & "'" & rs("Ref Address3") & "', " _
            & "'" & rs("Ref Post Code") & "', " _
            & "'" & rs("Ref Tele No") & "', " _
            & "'" & rs("Ref Fax") & "', " _
            & "'" & rs("E Mail Address") & "');"
            
        Debug.Print sql
        DoCmd.RunSQL sql

        rs.MoveNext
    Wend

Where sql is a string copied from the query. This works fine as the debug shows the exact sql I am expecting. However I keep getting "data type mismatch" errors everytime I try to run the code.

Everything in the referrer table is set to text, exact the key which is autonumber. I removed all validation and such as I thought maybe that was it, but still I get an error.

I pasted the sql generated into the access auery builder and I still got the same errror.

Any help would be greatly appreciated.

Thanks,
igkuk7

 

[KeithG]

which line of code do you receive the error on? Also I don't think you need to use a loop becuase the query should only run once.

 

[EMP]

Everything in the referrer table is set to text, exact the key which is autonumber.

If referrerID is an autonumber field, you don't need to insert anything into it.

sql = "INSERT INTO Referrer ([b][color=red]referrerID,[/color][/b] name, street, town, " _
            & "county, postcode, telephone, fax, email) VALUES " _
            & "([b][color=red]'',[/color][/b] '" & rs("Ref Name") & "', " _
            ........................

^

 

[MarkK]

Wouldn't it be way faster to write it as an append query...?

INSERT INTO newTable ( field1, field2, field3 ) 
SELECT fieldOne, fieldTwo, fieldThree 
FROM oldTable;

And you can do all the work using queries in the designer. No code at all.

 

[igkuk7]

If referrerID is an autonumber field, you don't need to insert anything into it.

Thanks EMP, that was it. It worked now.

I always thought if something was autonumber you could just insert '' and it would ignore it. Apparently not.

Thanks again.