Insert entry in table with only default values

[deneguil]

Hello, I am very new with using Access as I come more from a pure SQL background.

I have been having trouble with inserting into a table using only its default values.

I have a table with several Yes/No fields and each of these fields have the default value set to `=No` in the table creation view.

My issue comes when I try inserting into it using VBA. I tried running DoCmd.RunSQL "INSERT INTO table DEFAULT VALUES"and INSERT INTO table () VALUES () but every time I get hit with the error 3134.

I don't know what I'm doing wrong as for me it should be working, any help is appreciated

Thank you

 

[ebs17]

In which SQL dialect do your attempts work?

? accesserror(3134)
Syntax error in the INSERT INTO statement.

To the point: In the record to be created, at least one value must be written correctly and thus assigned so that the default values are adopted as field contents. However, this basic behavior should be the same across all SQL dialects.

 

[deneguil]

My first attemps works with MySQL last time I tried it.

So I would have to give the value for one field for all of the others to correctly use their default values

 

[cheekybuddha]

Not possible (I'm fairly certain) with Access SQL.

If you have a single field you can put a value into, then the rest will populate with defaults, eg:

INSERT INTO YourTable (LastUpdated) VALUES (Now());

 

[theDBguy]

Hi. Welcome to AWF!

Inserting default values aside, I am wondering what the multiple yes/no fields are for. Is your table structure properly normalized?

 

[Gasman]

If you have default values, then just inserting a record would be enough, surely?

 

[AHeyne]

Your table has no primary key?

 

[Pat Hartman]

Why would you insert essentially "empty" rows? That creates garbage data. If you tell us your actual objective, we can probably offer a better suggestion.

 

[Bennet]

I wanted to do this recently and hit the same problem. The reason was that I wanted to generate a new record for the sake of getting its ID number which I then wanted to use in other tables. Yes, there would be some more info to write back to that record later, but for now I just wanted a new "blank" record with default values only and a new ID number.

Ended up with the same conclusion that's been mentioned above - you have to write something to the table to generate a new record. Couldn't get the "default values" thing to work.

 

[Gasman]

I wanted to do this recently and hit the same problem. The reason was that I wanted to generate a new record for the sake of getting its ID number which I then wanted to use in other tables. Yes, there would be some more info to write back to that record later, but for now I just wanted a new "blank" record with default values only and a new ID number.

Ended up with the same conclusion that's been mentioned above - you have to write something to the table to generate a new record. Couldn't get the "default values" thing to work.

When you create a new record, only then are the default values populated.

 

[cheekybuddha]

Couldn't get the "default values" thing to work.

Access SQL does not have INSERT INTO YourTable DEFAULT VALUES; as part of its dialect - some other RDBMS's do.

 

[Pat Hartman]

Couldn't get the "default values" thing to work.

And that is a good thing since creating bad records, even temporarily, is poor practice