Error 2196 "Object already exists"

[wingnut69]

Hi

I am building a small database using PostgreSQL as the back end.

I am trying to programmatically insert data into a table. I have done this more times than I care to think about but this time I keep getting this error 2196 Object already exists"

The table is a 3 column table with PK, data to be inserted and an Postgres date/time key with its default set to now(). I know from previous experience that this works fine in Postgres. The table is nothing more than an audit trail of receipts. A user can have as many receipts for the same payment as they want. All this table does is capture the primary key of the payment and insert it into the second column and inserts by default the date/time of the new receipt being generated.

The columns are:

receiptdetailid - serial, primary key
paymentid - integer, default 0
receiptdate - date without time zone default now()

I can insert the data using PGAdmin 3 by a normal insert statement
"INSERT INTO receiptdetail (paymentid) values (25001)"
for example.

When I try to insert it twice using Access, I get this error. My suspicion is that it reads that a value of 25001 already exists and refuses to insert it into the table, even though the timestamp will be different. When I insert it using the backend software there is no problem.

What peeves me about this is that this action is intiated by a SQL string 'strINSERT' and the insertion is done by:

Set db = currentdb()
Set qdf = db.CreateQueryDef(strINSERT)

With qdf
.Connect = fnConnString 'being the ODBC connection to Postgres
.SQL = strINSERT
.ReturnsRecords = False
.Execute
.Close
Set qdf = Nothing
End With

And with Access, the variable is NOT an object unlike other languages even though the message states that it is.

Should I create another column, say integer and increment it by 1 every time a new record is added? Could this break that error even though it is a total waste of time?

Any ideas?

Thanks in advance

 

[wingnut69]

Re: Error 2196 "Object already exists" Solved

My suggestion worked. I now have a totally useless field for Postgres called 'dummy' and Access happily increments it by code and then inserts it. How can I close this thread?

 

[pbaldy]

You can edit the thread and change the title to solved. I'm curious about the error though. My first guess would have been the fact that you're creating a query that already exists. I personally use a saved pass through query and just change the SQL of it.

 

[wingnut69]

Yes. I was a little amazed myself, especially as the variable strINSERT gets destroyed as soon as the sub is exited. That's why my guess that there must have been some background checking prior to inserting the data sounds correct. It could be a case of over engineering by Microsoft as it certainly wasn't from the Postgres end of things.

Thanks for your thoughts.

 

[Galaxiom]

Yes. I was a little amazed myself, especially as the variable strINSERT gets destroyed as soon as the sub is exited. That's why my guess that there must have been some background checking prior to inserting the data sounds correct.

Your hypothesis is completely off the mark. Your code causes the Object Already Exists error.

Set qdf = db.CreateQueryDef(strINSERT)

This line creates a querydef with a name that is the value of strINSERT. You wouldn't see it in the user interface until the QueryDefs collection and database windows are refreshed.

You can insert different values because strINSERT will give different names the querydefs. But inserting the same value a second time will attempt to create a new querydef with the same name. Hence the error.

Adding the extra useless column with an incremented value will change the name given to the querydef and allow another to be created.

 

[Galaxiom]

BTW Giving a name to a querydef on creation automatically saves it to the QueryDefs Collection. Leave the name out and the qdf variable can be executed without ever saving it.

 

[wingnut69]

Hi Galaxiom

Your explanation is more sound than my hypothesis. Just one question. When I Set qdf = nothing
I thought that was the equivalent of blowing it away, never to be seen again. Please correct me if I'm wrong but I still don't understand why something which I thought was no more at the end of the sub still exists beyond the life of it. Obviously not from what you are saying.

The curiosity then is how do the other qdfs I create using the same name throughout the database not conflict with each other on reusage?

Sorry, the above depicts a senior's moment.

What I have realised thanks to your explanation is that Access gets the name not only from the variable name (strINSERT) but also the contents of strINSERT. If they stay the same then the name is the same as previously and thus there lies the basis of error 2196 and as Access doesn't realise that it is writing to a table with an automatic timestamp (it can only base the decision on what it sees in its own system) it calls the error. Thanks for the education.