Insert Into not Inserting 4

[JackD4ME]

Hi All,
I have an "Insert Into" statement that cycles through while statement. The db object is correct, the Insert Into syntax is correct, and the recordset object is set correct. The code functions correctly except that it does not add the record to the target table.

db.Execute " Insert Into tblLease (UnitId, LeaseNumber, LeaseName, OpId, PartFact, Jurisdiction, Region) Values ('" & _
rst.Fields![UnitId] & "','" & rst.Fields![LeaseNumber] & "','" & rst.Fields![LeaseName] & "','" & rst.Fields![OpID] & "','" & _
rst.Fields![PartFact] & "','" & rst.Fields![Jurisdiction] & "','" & rst.Fields![Region] & "');"

Any ideas why this code does not add the record to the table?

Thanks,
Jack D

 

[Jaco]

Maybe due to primary key violation... ?

Jaco

 

[CCLINT]

Jaco is probably correct. That is what I also suspect.

Is this DAO?

If so, add the optional parameter:
,dbFailOnError
to the end of the execute line.

See what error is returned. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[JackD4ME]

You guys were close, there were 2 fields that are currently null due to being test data. The added code allowed me to track the problem as I just "discovered" the insert into statement. Thank you so much for the insight!

Semper Fi
Jack D.

 

[SamyO]

I'm having a similar problem (Insert Into statement works but the tables are still empty). However, I don't have any keys on my tables because I don't really need them. I tried using the debugger to find out what the SQL statement looks like it and it looks fine. Everything runs without errors.

I'm dynamically creating a new database based on a file. Then I build SQL Insert statements (also based on the contents of the file) and run them to populate the table. Then I close the database.

I'm wondering if the fact that I'm creating the database and trying to insert rows into its tables without any steps in between (such as closing and reopening, 'commiting') is causing this? I'm familiar with SQL and Oracle but this is my first attempt at creating a database in VB.

By the way, all the tables and fields are there (I checked with Access).. but they're all empty. I also tried using dbFailOnError but I can't get it to compile with it.

m_Db.Execute(sSQLCmd, dbFailOnError)
Gives me a syntax error (expected '=')

So I tired
MsgBox (m_Db.Execute(sSQLCmd, dbFailOnError))
and
tmpStr = m_Db.Execute(sSQLCmd, dbFailOnError)
which gives me a runtime error (expected function or variable)

Thanks,
Samy

 

[SamyO]

I continued experimenting and I found out how to use dbFailOnError. I should be able to find out why it's not working now. Sorry about the last post.

Samy