creating tables with VBA

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi all,

Could someone point me a website where I can get some information and
examples on how to create tables with VBA code (ADO reference if possible).

Thx,
Ludovic



--
 
There is an easier way. Access has a limited set of Sql DDL commands. But
what's there covers probably 90% of what most folks use.

Here are a few examples:

CREATE Table MyTable
(
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyByte BYTE,
MyInteger SMALLINT,
MyLong LONG,
MySingle SINGLE,
MyDouble DOUBLE,
MyDate DATETIME,
MyCurrency CURRENCY,
MyYesNo YESNO,
MyMemo MEMO,
MyOle OLEOBJECT,
MyBinary BINARY(100)
)

CREATE INDEX [IX_MyLong] ON [MyTable]([MyLong])

or

CREATE UNIQUE INDEX [IX_MyLong] ON [MyTable]([MyLong])

to make changes in an existing table.

ALTER Table MyTable
ADD COLUMN
NEWText CHAR(50),
NEWNum LONG,
NEWDate DATETIME

If what you need can not be done using access DDL queries than you are stuck
with ADOX or the native DAO Access stuff. There should be some docs in the
Access Help files, and undoubtly lot's more available on the web.

Ron W


Daniel Pineault said:
Here are 2 links worth checking out

http://vbadud.blogspot.com/2007/11/create-database-with-ado-ado-create.html
http://groups.google.ca/group/micro...ms+access+vba+CreateTableDef#5fc4c0ad590a924b
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.
Click to expand...
 
Ron,

This works excelent. Is there a web site where I can find moreSQL ddl
commands for Access or a tutorial on this subject?

Thx, for your advice.
Ludovic


Ron Weiner said:
There is an easier way. Access has a limited set of Sql DDL commands.
But what's there covers probably 90% of what most folks use.

Here are a few examples:

CREATE Table MyTable
(
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyByte BYTE,
MyInteger SMALLINT,
MyLong LONG,
MySingle SINGLE,
MyDouble DOUBLE,
MyDate DATETIME,
MyCurrency CURRENCY,
MyYesNo YESNO,
MyMemo MEMO,
MyOle OLEOBJECT,
MyBinary BINARY(100)
)

CREATE INDEX [IX_MyLong] ON [MyTable]([MyLong])

or

CREATE UNIQUE INDEX [IX_MyLong] ON [MyTable]([MyLong])

to make changes in an existing table.

ALTER Table MyTable
ADD COLUMN
NEWText CHAR(50),
NEWNum LONG,
NEWDate DATETIME

If what you need can not be done using access DDL queries than you are
stuck with ADOX or the native DAO Access stuff. There should be some docs
in the Access Help files, and undoubtly lot's more available on the web.

Ron W


Daniel Pineault said:
Here are 2 links worth checking out

http://vbadud.blogspot.com/2007/11/create-database-with-ado-ado-create.html
http://groups.google.ca/group/micro...ms+access+vba+CreateTableDef#5fc4c0ad590a924b
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.
Click to expand...
Click to expand...
 
Google is your friend. http://www.google.com/search?hl=en&q=access+ddl
First few results look to be promising. Is there something in particular
you are looking for/

Ron W
Vsn said:
Ron,

This works excelent. Is there a web site where I can find moreSQL ddl
commands for Access or a tutorial on this subject?

Thx, for your advice.
Ludovic


Ron Weiner said:
There is an easier way. Access has a limited set of Sql DDL commands.
But what's there covers probably 90% of what most folks use.

Here are a few examples:

CREATE Table MyTable
(
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyByte BYTE,
MyInteger SMALLINT,
MyLong LONG,
MySingle SINGLE,
MyDouble DOUBLE,
MyDate DATETIME,
MyCurrency CURRENCY,
MyYesNo YESNO,
MyMemo MEMO,
MyOle OLEOBJECT,
MyBinary BINARY(100)
)

CREATE INDEX [IX_MyLong] ON [MyTable]([MyLong])

or

CREATE UNIQUE INDEX [IX_MyLong] ON [MyTable]([MyLong])

to make changes in an existing table.

ALTER Table MyTable
ADD COLUMN
NEWText CHAR(50),
NEWNum LONG,
NEWDate DATETIME

If what you need can not be done using access DDL queries than you are
stuck with ADOX or the native DAO Access stuff. There should be some
docs in the Access Help files, and undoubtly lot's more available on the
web.

Ron W


Daniel Pineault said:
Here are 2 links worth checking out

http://vbadud.blogspot.com/2007/11/create-database-with-ado-ado-create.html
http://groups.google.ca/group/micro...ms+access+vba+CreateTableDef#5fc4c0ad590a924b
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.



:

Hi all,

Could someone point me a website where I can get some information and
examples on how to create tables with VBA code (ADO reference if
possible).

Thx,
Ludovic
Click to expand...
Click to expand...
Click to expand...
 
I do agree on google, but you need to know what your looking for. in this
case i had no idea about an SQL ddl. Beside that, in this news group there
are some MVP who realy have excelent solutions from which i tent to learn
alot.

What I was looking for, further on your answer is, how to define the default
format and value in lets say a date field. Surly when I did manage that I
will come up with a new idea or question on the subject, thats why I asked
if there was some info on the net covering the topic.

I will take a look at the pages google comes up with.

Thx,
Ludovic


Ron Weiner said:
Google is your friend. http://www.google.com/search?hl=en&q=access+ddl
First few results look to be promising. Is there something in particular
you are looking for/

Ron W
Vsn said:
Ron,

This works excelent. Is there a web site where I can find moreSQL ddl
commands for Access or a tutorial on this subject?

Thx, for your advice.
Ludovic


Ron Weiner said:
There is an easier way. Access has a limited set of Sql DDL commands.
But what's there covers probably 90% of what most folks use.

Here are a few examples:

CREATE Table MyTable
(
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyByte BYTE,
MyInteger SMALLINT,
MyLong LONG,
MySingle SINGLE,
MyDouble DOUBLE,
MyDate DATETIME,
MyCurrency CURRENCY,
MyYesNo YESNO,
MyMemo MEMO,
MyOle OLEOBJECT,
MyBinary BINARY(100)
)

CREATE INDEX [IX_MyLong] ON [MyTable]([MyLong])

or

CREATE UNIQUE INDEX [IX_MyLong] ON [MyTable]([MyLong])

to make changes in an existing table.

ALTER Table MyTable
ADD COLUMN
NEWText CHAR(50),
NEWNum LONG,
NEWDate DATETIME

If what you need can not be done using access DDL queries than you are
stuck with ADOX or the native DAO Access stuff. There should be some
docs in the Access Help files, and undoubtly lot's more available on the
web.

Ron W


message Here are 2 links worth checking out

http://vbadud.blogspot.com/2007/11/create-database-with-ado-ado-create.html
http://groups.google.ca/group/micro...ms+access+vba+CreateTableDef#5fc4c0ad590a924b
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.



:

Hi all,

Could someone point me a website where I can get some information and
examples on how to create tables with VBA code (ADO reference if
possible).

Thx,
Ludovic
Click to expand...
Click to expand...
Click to expand...
 
I do not believe that Access DDL supports default values. At least I was
unable to find a DDL statement that would allow me to set the default value
of a DateTime field to Now(). The Syntax to do this in using T-Sql in Msft
Sql Server would be:

ALTER Table MyTable ALTER COLUMN mydate CONSTRAINT AddDateDflt DEFAULT
getdate()

However this and many variants that I tried do not work in Access.

Msft's site for Access DDL ( http://support.microsoft.com/kb/180841 ) makes
no mention of Default Values.

This appears to be the 10% of the time that you'll have to rely on DAO or
ADOX.

Ron W
I do agree on google, but you need to know what your looking for. in this
case i had no idea about an SQL ddl. Beside that, in this news group there
are some MVP who realy have excelent solutions from which i tent to learn
alot.

What I was looking for, further on your answer is, how to define the
default format and value in lets say a date field. Surly when I did manage
that I will come up with a new idea or question on the subject, thats why
I asked if there was some info on the net covering the topic.

I will take a look at the pages google comes up with.

Thx,
Ludovic


Ron Weiner said:
Google is your friend. http://www.google.com/search?hl=en&q=access+ddl
First few results look to be promising. Is there something in particular
you are looking for/

Ron W
Vsn said:
Ron,

This works excelent. Is there a web site where I can find moreSQL ddl
commands for Access or a tutorial on this subject?

Thx, for your advice.
Ludovic


"Ron Weiner" <rweineratworksritedotcom> wrote in message
There is an easier way. Access has a limited set of Sql DDL commands.
But what's there covers probably 90% of what most folks use.

Here are a few examples:

CREATE Table MyTable
(
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyByte BYTE,
MyInteger SMALLINT,
MyLong LONG,
MySingle SINGLE,
MyDouble DOUBLE,
MyDate DATETIME,
MyCurrency CURRENCY,
MyYesNo YESNO,
MyMemo MEMO,
MyOle OLEOBJECT,
MyBinary BINARY(100)
)

CREATE INDEX [IX_MyLong] ON [MyTable]([MyLong])

or

CREATE UNIQUE INDEX [IX_MyLong] ON [MyTable]([MyLong])

to make changes in an existing table.

ALTER Table MyTable
ADD COLUMN
NEWText CHAR(50),
NEWNum LONG,
NEWDate DATETIME

If what you need can not be done using access DDL queries than you are
stuck with ADOX or the native DAO Access stuff. There should be some
docs in the Access Help files, and undoubtly lot's more available on
the web.

Ron W


message Here are 2 links worth checking out

http://vbadud.blogspot.com/2007/11/create-database-with-ado-ado-create.html
http://groups.google.ca/group/micro...ms+access+vba+CreateTableDef#5fc4c0ad590a924b
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.



:

Hi all,

Could someone point me a website where I can get some information and
examples on how to create tables with VBA code (ADO reference if
possible).

Thx,
Ludovic
Click to expand...
Click to expand...
Click to expand...
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Back
Top