Delte Access record using Excel VBA

Albrecht · October 12th, 2010, 08:31 AM

Hi

I'm trying to delete a single record in a Access database using the primary key of the record.

Code:

Dim sSQL As String

Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open DBsti

sSQL = "DELETE FROM IdeDB WHERE tbIde = '" & tbOpgaveNr & "'"

conn.Execute sSQL

conn.Close
Set conn = Nothing

DBsti (Path to database)
tbIde (Primary key)
tbOpgaveNr (Number (primary key) of record shown in userform)

If I change form primary key to a natural key it works fine but when I run the code I get an error from program line conn.Execute sSQL

"Runtime error 2147217904(80040e10) No value given for one or more required parameters"

I have also tried changing the code to:

Code:

sSQL = "DELETE FROM IdeDB WHERE tbIde = " & tbOpgaveNr & ""

and it is still the same.

What am I doing wrong

gbianchi · October 12th, 2010, 08:42 AM

HI there..

Try "DELETE * FROM table WHERE field = " & yourcode

In access the * is mandatory for deleting rows.

Albrecht · October 12th, 2010, 08:46 AM

Hi gbianchi

Thank you for a very quich repply. I have tried the * and it is still the same.

gbianchi · October 12th, 2010, 08:48 AM

If you try the query in access, what field does it say that is missing?

Albrecht · October 12th, 2010, 08:53 AM

Hmm.. I have not tried that and I don't know much about VBA in Access. How should it look like in Access?

gbianchi · October 12th, 2010, 08:54 AM

NO VBA in access, Can you just open the database and try the query inside of it?? usually access has a lot better error messages when you execute query inside of it...