command to create database

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Hi,

I'm developing a web application and I'm using Access
2002. I would like to ask regarding the command to create
a database in Access 2002. My intention is to create a
new database from my application such as ASP, JSP,
ColdFusion.

Please advice. Thanks in advance.
 
My intention is to create a
new database from my application such as ASP, JSP,
ColdFusion.
Click to expand...

' create the new database
set cat = createobject("ADOX.Catalog")
cat.Create "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=d:\temp\temporary.mdb"
set conn = cat.ActiveConnection

' create a new table
strSQL = "CREATE TABLE Test " & vbCrLf & _
"( TestID AUTOINCREMENT " & vbCrLf & _
" CONSTRAINT pk PRIMARY KEY, " & vbCrLf & _
" Textfield VARCHAR(20) NOT NULL " & vbCrLf & _
" DEFAULT ""-""" & vbCrLf & _
")"

' checking it out
WScript.Echo strSQL

' Make it so...
On Error Resume Next
conn.execute strSQL, dwRecordsAffected, adCmdText + adExecuteNoRecords


' Hope that helps


' Tim F
 
-----Original Message-----


' create the new database
set cat = createobject("ADOX.Catalog")
cat.Create "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=d:\temp\temporary.mdb"
set conn = cat.ActiveConnection

' create a new table
strSQL = "CREATE TABLE Test " & vbCrLf & _
"( TestID AUTOINCREMENT " & vbCrLf & _
" CONSTRAINT pk PRIMARY KEY, " & vbCrLf & _
" Textfield VARCHAR(20) NOT NULL " & vbCrLf & _
" DEFAULT ""-""" & vbCrLf & _
")"

' checking it out
WScript.Echo strSQL

' Make it so...
On Error Resume Next
conn.execute strSQL, dwRecordsAffected, adCmdText + adExecuteNoRecords


' Hope that helps


' Tim F

.
Click to expand...
Hi Tim,

Thanks for the info. I'm kind of a beginner here, do you
mind explaining the codes?

Another thing, the command looks like MYSQL commands. Are
you sure they are the commands for MS Access?
I'm sorry, I don't mean to doubt, I'm just trying to
prevent any ambiguity.

Please advice. Thanks a lot.
 
Thanks for the info. I'm kind of a beginner here, do you
mind explaining the codes?
Click to expand...

Since you said you were putting together asp pages, I kind of assumed that
you had some familiarity with VBScript..

The first thing to do is to create a Catalog object, which is responsible
for marshalling all the tables, relationships and queries etc for a
database. The next line makes this empty Catalog create a new Jet database
as the file d:\temp\temporary.mdb, and to remain connected to it. The third
line sets the variable 'conn' to its ActiveConnection (i.e. to the new,
empty database) because that is the connection we are going to be using to
populate the database.


This part is very simple: it sets up a CREATE TABLE command and gets the
connection object to execute it. I have removed some lines that are there
for error trapping and debugging.
Another thing, the command looks like MYSQL commands. Are
you sure they are the commands for MS Access?
Click to expand...

Yes and no: they are VBScript commands (althought the same lines would work
perfectly well in Access or Excel or any VBA host). Remember that any
database access is going to be mediated by SQL statements, and that may be
what you are recognising. For most database work, Access is only required
if you are using its UI objects like forms and reports: all the data
manipulation happens directly between VBA/ VBS and Jet.
I'm sorry, I don't mean to doubt, I'm just trying to
prevent any ambiguity.
Click to expand...
Absolutely no offence taken: but I am a little nonplussed by the phrase you
began the original post with, "I'm developing a web application" -- you are
going to need a great deal of familiarity with all of the above, and more,
if that is really what you mean. If it's a language issue, then all the
code lines above have exact counterparts in JScript, or in Perl, or
whatever you are implementing your web application in. But you are going to
get nowhere without facility with some kind of programming toolkit.

Best wishes


Tim F
 
Back
Top