Access SQL VBA, YesNo field, Default Value (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 06:25
Joined
Jul 9, 2015
Messages
424
Hello everyone,

Today's question:
What is the correct syntax for creating a table with VBA, using an YesNo field, and setting it's default value to NO/0/False?

Code:
sSQL = "CREATE TABLE TempSupercede (ItemID_FK INT, SupplierID_FK INT, PartNumber TEXT, DiscPart YesNo DEFAULT 0, Supercede YesNo DEFAULT 0)"

This does not work, syntax error. IF I remove the default constraints, it runs. Using NO and FALSE also fail.

According to W3 Schools (W3), it should work...

Thanks!
Mike
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 28, 2001
Messages
26,998
Perhaps try doing the CREATE TABLE and then as a second action, do an ALTER TABLE to alter the field defaults?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
how are you executing the sql? seems according to this link it needs to be ado rather than dao

 

mjdemaris

Working on it...
Local time
Today, 06:25
Joined
Jul 9, 2015
Messages
424
how are you executing the sql? seems according to this link it needs to be ado rather than dao

Code:
DoCmd.RunSQL sSQL
 

mjdemaris

Working on it...
Local time
Today, 06:25
Joined
Jul 9, 2015
Messages
424
Perhaps try doing the CREATE TABLE and then as a second action, do an ALTER TABLE to alter the field defaults?
I was considering this Doc, while writing this post. Going to try it in a minute.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
suggest try the recommendation in the link I provided
 

mjdemaris

Working on it...
Local time
Today, 06:25
Joined
Jul 9, 2015
Messages
424
So, what I'm seeing is that I must use ADO to create this table with a default value on a field?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
yes - but since the solution worked for someone with an identical problem it seems worth a try. It's a new one on me but it would appear that

currentproject.Connection.execute

executes as ADO

docmd.runsql is DAO

if you google it, you will find numerous other links that say the same thing - I just picked one that seemed fairly brief

edit: you just said you had a syntax error, not what the error actually reported - but the issue is with Default, not the 0
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:25
Joined
Mar 14, 2017
Messages
8,738
This comment may invite some challenges, which I welcome as I may learn something new here.
But, I have not found a whole lot of reason to use DDL type code in Access. It seems that often times in cases where someone is using it, there is something more broadly wrong (or could be simpler) about their overall process.
For example, if importing data, usually I'd just import the data to a raw or staging table and then deal with it as-is, as it imported.
I definitely do not leverage this method to perform regular table design, but are there cases where experienced developers do in fact find it to be the best route?

I mean, it's not like you build an app where a user-driven process flow might suddenly decide it needs an index on a table....or do you?

It has always struck me as a very, very sparsely documented capability in MS Access. I'm not saying actually undocumented, but sparse....I shy away from those things if possible.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
I use it for importing large amounts of data where I need to create a temporary unindexed table in a temporary database, import and then add indexes. Much quicker than importing to an indexed table. Yes could use a table 'template' but there have been occasions where it doesn't work due to small changes required 'on the fly' or the import module gives the user the opportunity create or modify their own import profile without me getting involved.

I've also used it during development as a 'script' to create or recreate a test database and can comment out bits of the script which I don't want to run again if doing a partial reset.

Finally I've used it where I need to update a client's BE and but don't have access to it - can just send the client a small .accde and ask them to run it

Could use similar code to create tabledefs instead I guess but that was the way I learned on sql server.

But I don't use it as part of normal user interaction

And in respect of this thread, I've not set defaults so was unaware of the ADO issue

If using currentproject.Connection.execute is ADO, brings some ideas to mind how that might be useful. Will need to find some time to investigate.

Here is an example from my AccessStudio free version
[CODE]/*
usysCounter is a useful routine to generate multi line queries such as when a number of records are required to be generated from a single value
highlight and execute each block of code as required
or remove the block comments to execute all in one pass
*/

/*DROP TABLE usysCounter;--only use if you want to start from scratch*/

/*CREATE TABLE usysCounter ( num long);*/

/*DELETE * FROM usysCount;-- only use if you want to refresh the data */

/*
INSERT INTO usyscounter (num) VALUES (0);
INSERT INTO usyscounter (num) VALUES (1);
INSERT INTO usyscounter (num) VALUES (2);
INSERT INTO usyscounter (num) VALUES (3);
INSERT INTO usyscounter (num) VALUES (4);
INSERT INTO usyscounter (num) VALUES (5);
INSERT INTO usyscounter (num) VALUES (6);
INSERT INTO usyscounter (num) VALUES (7);
INSERT INTO usyscounter (num) VALUES (8);
INSERT INTO usyscounter (num) VALUES (9);

SELECT * FROM usyscounter;--check it has populated as expected*/

/*
to create a query from here (I use usysCount) - enter the name you want in the name box above, highlight the sql code below and click on Save as Query

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;
*/

/*This example could be used to create a complete database - in a new db, add in Access Studio, then execute the code - as development continues, highlight relevant code to rebuild*/
[/CODE]
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:25
Joined
Mar 14, 2017
Messages
8,738
Thanks, CJ. That's good information to know on when to use.
Definitely makes sense on the index issue especially.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
I've just loaded AccessStudio to the Code Repository forum if you want to take a look
 

isladogs

MVP / VIP
Local time
Today, 13:25
Joined
Jan 14, 2017
Messages
18,186
As already stated, you can only use DEFAULT or CONSTRAINT in a CREATE TABLE or ALTER TABLE statement using ADO e.g.

Code:
Dim strSQL As String
strSQL = "CREATE TABLE Temp (ID INT, PartNumber TEXT(50), Bool BIT DEFAULT 0)"
CurrentProject.Connection.Execute strSQL

However, you can do so using DAO with TableDefs.
See http://allenbrowne.com/func-DAO.html#CreateTableDAO
 

mjdemaris

Working on it...
Local time
Today, 06:25
Joined
Jul 9, 2015
Messages
424
This comment may invite some challenges, which I welcome as I may learn something new here.
But, I have not found a whole lot of reason to use DDL type code in Access. It seems that often times in cases where someone is using it, there is something more broadly wrong (or could be simpler) about their overall process.
For example, if importing data, usually I'd just import the data to a raw or staging table and then deal with it as-is, as it imported.
I definitely do not leverage this method to perform regular table design, but are there cases where experienced developers do in fact find it to be the best route?

I mean, it's not like you build an app where a user-driven process flow might suddenly decide it needs an index on a table....or do you?

It has always struck me as a very, very sparsely documented capability in MS Access. I'm not saying actually undocumented, but sparse....I shy away from those things if possible.

I use it for importing large amounts of data where I need to create a temporary unindexed table in a temporary database, import and then add indexes. Much quicker than importing to an indexed table. Yes could use a table 'template' but there have been occasions where it doesn't work due to small changes required 'on the fly' or the import module gives the user the opportunity create or modify their own import profile without me getting involved.

I've also used it during development as a 'script' to create or recreate a test database and can comment out bits of the script which I don't want to run again if doing a partial reset.

Finally I've used it where I need to update a client's BE and but don't have access to it - can just send the client a small .accde and ask them to run it

Could use similar code to create tabledefs instead I guess but that was the way I learned on sql server.

But I don't use it as part of normal user interaction

And in respect of this thread, I've not set defaults so was unaware of the ADO issue

If using currentproject.Connection.execute is ADO, brings some ideas to mind how that might be useful. Will need to find some time to investigate.

Here is an example from my AccessStudio free version
[CODE]/*
usysCounter is a useful routine to generate multi line queries such as when a number of records are required to be generated from a single value
highlight and execute each block of code as required
or remove the block comments to execute all in one pass
*/

/*DROP TABLE usysCounter;--only use if you want to start from scratch*/

/*CREATE TABLE usysCounter ( num long);*/

/*DELETE * FROM usysCount;-- only use if you want to refresh the data */

/*
INSERT INTO usyscounter (num) VALUES (0);
INSERT INTO usyscounter (num) VALUES (1);
INSERT INTO usyscounter (num) VALUES (2);
INSERT INTO usyscounter (num) VALUES (3);
INSERT INTO usyscounter (num) VALUES (4);
INSERT INTO usyscounter (num) VALUES (5);
INSERT INTO usyscounter (num) VALUES (6);
INSERT INTO usyscounter (num) VALUES (7);
INSERT INTO usyscounter (num) VALUES (8);
INSERT INTO usyscounter (num) VALUES (9);

SELECT * FROM usyscounter;--check it has populated as expected*/

/*
to create a query from here (I use usysCount) - enter the name you want in the name box above, highlight the sql code below and click on Save as Query

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;
*/

/*This example could be used to create a complete database - in a new db, add in Access Studio, then execute the code - as development continues, highlight relevant code to rebuild*/
[/CODE]
Forgive me, but I don't understand how this code works, I think I'm missing something from this example.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:25
Joined
Feb 19, 2013
Messages
16,553
Not quite sure what you don’t understand. The post is about using data definition queries. The bit in green is a working example of several such queries from Access Studio and includes comments. You uncomment whichever bit of code you want to execute.

you can download a copy of Access Studio from the code repository forum here https://www.access-programmers.co.uk/forums/threads/access-studio-formatted-sql-and-more.323842/

Otherwise please clarify what you don’t understand
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:25
Joined
Feb 19, 2002
Messages
42,970
But, I have not found a whole lot of reason to use DDL type code in Access. It seems that often times in cases where someone is using it, there is something more broadly wrong (or could be simpler) about their overall process.
I agree. If the schema is properly defined, there should not be a need to add/modify tables on the fly using this method.

I happen to use it for one application I have that is sold to the public. This is necessary since I don't have access to the client's network. They need to apply the updates themselves. Usually they pay me to supervise but they don't have to. It is a semi-custom app that comes with a lot of training so you wouldn't find it shrink-wrapped at Best Buy. Occasionally when I create a new version, I need to add columns to existing tables or add new tables. I made the decision long ago that nothing would ever be deleted. The update code would rename the table or column to indicate that it wasn't used any longer but no data would be removed, ever. The app has been out in the wild since 2007 which is a pretty long run. My partner isn't marketing it any more so we aren't actively soliciting new clients although one occasionally finds us.

The app can be installed with either an ACE BE or SQL Server so my updates also have to come in two flavors. I create a script for the DBA to run to update the SQL BE and for the ACE BE, I create a new app that checks versions and then runs a bunch of DDL queries. I number them to prevent accidents and make them easier to manage. I actually use an excellent tool that generates the scripts which saves me a huge amount of time. It's called SQL Examiner. It doesn't do Jet/ACE so I take the two SQL Server versions and compare them. The app generates a script that makes dbA look like dbB. That is all I need except for a few checks to make sure that the script is supposed to run on the version the client has. We can't have them trying to convert the wrong version. Then I modify the script to conform to Access DDL syntax and create individual DDL queries since Access can't run a script and you know me, I really don't like working with embedded SQL so I can easily test the DDL queries individually.
DEA_DDLqueries.JPG
 

Users who are viewing this thread

Top Bottom