simple syntax question. Please help!

[Sausagefingers]

Hi,
What appears to be wrong with the following:

Dim strSQL As String
strSQL = "ALTER TABLE [tbl_zone] ALTER COLUMN [blIsActive] BIT DEFAULT '1';"
DoCmd.RunSQL strSQL

I'm getting a runtime error every time, what have I missed?


Thanks in advance

 

[Rabbie]

Hi,
What appears to be wrong with the following:

Dim strSQL As String
strSQL = "ALTER TABLE [tbl_zone] ALTER COLUMN [blIsActive] BIT DEFAULT '1';"
DoCmd.RunSQL strSQL

I'm getting a runtime error every time, what have I missed?


Thanks in advance

From doing some tests it looks lke BIT is not a recognised field type in Access and that's why you are getting the error. Try looking up datatype in Access Help to see what is permitted

 

[boblarson]

Not sure about the "Default syntax" but a yes/no field in Access should work with BIT as well (according to another MVP) but yesno should work and the values for a yes/no field are either zero (0) or negative one (-1) not one (1). So, try doing

strSQL = "ALTER TABLE [tbl_zone] ALTER COLUMN [blIsActive] YESNO DEFAULT '-1';"

 

[Sausagefingers]

Thanks for your input, its much appreciated.

I had no luck with the modifications though, which leads me to believe I should be looking at another approach.

I need to generate five tables on the fly. All related and all generated in such a way the they populate with data in the correct order.

This is by far the biggest Access challenge I have undertaken, if I can get it right, it will save me endless hours of populating the tables manually. The resulting tableset will contain in excess of 30,000 populated fields.

I'm currently looking at DDL as an option:

http://allenbrowne.com/func-DDL.html

I got immediate results from the sample code which is always a positive experience. I need to know whether this is a better option than using straight SQL before I embark on the project. What do you think?

Thanks in advance

 

[WayPay]

I need to generate five tables on the fly. All related and all generated in such a way the they populate with data in the correct order.

Have a look at Make-Table Queries, this may be just what you need. On the other hand, you may only think you need to generate tables on the fly . Not enough data .

I need to know whether this is a better option than using straight SQL before I embark on the project. What do you think?

I think those examples are straight SQL. What do you think of as 'straigh SQL'?