Can these SQL Table Definitions be used?

[Cheese]

I would like to use the SQL Table Definitions available here to create a
Microsoft Access database:

http://wireless.fcc.gov/uls/data/documentation/pa_ddef42.txt

If this is possible, how would I do it?

 

[Allen Browne]

You can use similiar DDL queries in Access if you execute them in vba code
using ADO/ADOX.

You cannot place multiple CREATE TABLE statements in one query.

Here's an example of creating a table under ADOX:
http://allenbrowne.com/func-ADOX.html#CreateTableAdox

Here's a list of the data types to use in ADOX (and other libraries)
compared to the Access interface:
http://allenbrowne.com/ser-49.html

 

[Dirk Goldgar]

I would like to use the SQL Table Definitions available here to create a
Microsoft Access database:

http://wireless.fcc.gov/uls/data/documentation/pa_ddef42.txt

If this is possible, how would I do it?

You'd need to set the database to use ASNI-92 SQL syntax. That's an option
on the Tools -> Options... dialog's Tables/Queries tab. You can't run them
all as a batch, because Access won't accept that, but you could cut out each
individual statement and execute it separately.

I notice that there are NUMERIC -- a.k.a. DECIMAL -- fields there, and there
have been some bugs in Jet's handling of that data type. I don't know where
that stands now. You might look at how those fields are being used, and
consider whether another data type might be substituted.

 

[Albert D. Kallal]

I notice that there are NUMERIC -- a.k.a. DECIMAL -- fields there, and
there have been some bugs in Jet's handling of that data type. I don't
know where that stands now. You might look at how those fields are being
used, and consider whether another data type might be substituted.

I just did a quick cut/paste of the 1st table and with ansi92 turned on it
worked fine...

eg:
create table dbo.PUBACC_A2
(
Record_Type char(2) null,
etc.....

The above worked fine if you REMOVE the "database" "dbo" prefix.

However, what is REALLY interesting is that if you use the "dbo" prefix,
access WILL ASSUME the database is the SAME name but in access "default"
dir.

So, if one uses "dbo", then the database MUST BE

C:\Documents and Settings\Albert\My Documents\dbo.mdb

If the dbo is dropped, then access will not care where the mdb is currently
placed...

I not really worked with the decimal types, but certainly some people been
using them without problems....

So, to the orignal poster, you can cut/paste into the sql editor and hit the
run key....it will create the table for you. However, you MUST DO ONE table
at a time.

If you don't want to do all those cut/paste (you have to do one at a time),
then I would consider writing a import rotuine in vba to execute the whole
script. Or, simply fire up the free editon of sql server paste in the text,
run and then export the database to ms-access.....

 

[Dirk Goldgar]

The above worked fine if you REMOVE the "database" "dbo" prefix.

Darn! I meant to mention that, but I forgot. Thanks for pointing that out,
Albert.