Creating Database on the fly? How does Access handle string fields?

[Chuck Sweet]

Two very simple questions: Is it possible to create an Access database on the fly using ADO? If so, how?

Second: How does access handle string fields? If I create a field of 20 characters, and I have 3 characters of data, does it store spaces or lock up the memory for 20 characters or does it chop the field down to only the amount of space necessary?

Thanks,
Chuck

[JHausmann]

Item 1, yes. Tom Clunie has posted this in the past, a search of this forum should get you what you want.

Item 2. There is no varchar in Access, it's going to use the size you specify.

[BruceG]

Regarding item #2, I beg to differ. In Access, when you declare a field as Text, the length you declare is the MAXIMUM number of characters that can be stored in the field - but it is a variable-length field and will only store the number of characters necessary. This is true when you design a table in the Access UI or use a SQL CREATE TABLE statement, such as:

dbMyDB.Execute "CREATE TABLE MyTable (MyField TEXT(20))"

Here's an obscure bit of trivia. The ONLY way you can create a FIXED length character field in Access is with SQL and declare a field as CHAR rather than TEXT:

dbMyDB.Execute "CREATE TABLE MyTable (MyField CHAR(20))"

[Chuck Sweet]

Thanx for your help JHausmann and BruceG. I'm posting this for anyone following this line. This is Clunietp's code for creating a DB in ADO. Thanx to Clunietp for posting it in the first place.

-Chuck
********************************************************
Clunietp
Guru
Registered: Oct 1999
Posts: 1877

Create a database using ADO:

code:

'uses ADO 2.x for DDL and security
Dim strConnectionStringOfNewDB As String
Dim objCat As ADOX.Catalog

'instantiate catalog object
Set objCat = New ADOX.Catalog

'connection string of new db -- includes driver/db type and location
strConnectionStringOfNewDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewDB.mdb"

'create it
objCat.Create strConnectionStringOfNewDB

'cleanup
Set objCat = Nothing