creating tables in ms access

[aloysius]

hi guys..is there a way i can create tables from a web page? i mean could i create a button on the webpage so that when the user clicks, it automatically creates a table in ms access? pls help me..i need help..

[buntine]

Hey mate,

There sure is!

You have to use the CREATE TABLE SQL keyword. The basic syntax is below:

CREATE TABLE table_name (column1 dataType, column2 dataType)

To put in context for you, this example will create a table named 'tblTest' and add two fields to it -- intAge and varName.

CREATE TABLE tblTest (intAge integer(3), varName varchar(200))

The preceding example will create the new table. integer(3) will create a new field which can hold integer (numeric) values. The 3 inside the paranthesis determines the length of the value; so 999 is the highest.
varchar is similar to the 'variant' data type. It can contain alphanumeric characters and special characters like _, ~, and *.

Regards,
Andrew Buntine.

[aloysius]

thank you so much...but i plan to put it online...this means that i must put the asp codes somewhere in the webpage...where do i put it? and is there a way for the user to trigger a control to create a new table (i.e. like hitting on a button or something)

[buntine]

You would execute the query using an ADO Connection object. Have the page invoked when a user clicks a button.

dim conn, conn_string, sql

set conn = server.createObject("ADODB.Connection")
conn_string = "DBQ=" & server.MapPath("yourDBpath") & ";"
conn_string = conn_string & "Driver={Microsoft Access Driver (*.mdb)}"
conn.open (conn_string)

sql = "CREATE TABLE tblTest (intAge integer(3), varName varchar(200))"
conn.execute (sql)

You will have to alter the connection string to comply with your server settings.

Regards,
Andrew Buntine.

[aloysius]

thank you so much...but i need the part that allows the user to hit the button and get the new table..

[buntine]

Ok.. This is what you gotta do.

<!-- Put this code in your HTML page. -->
<form action="createTable.asp" method="post">
<input type="submit" name="btnSubmit" value="Create Table" />
</form>


'|Put this in an ASP page named 'createTable.asp'
dim conn, conn_string, sql

set conn = server.createObject("ADODB.Connection")
conn_string = "DBQ=" & server.MapPath("yourDBpath") & ";"
conn_string = conn_string & "Driver={Microsoft Access Driver (*.mdb)}"
conn.open (conn_string)

sql = "CREATE TABLE tblTest (intAge integer(3), varName varchar(200))"
conn.execute (sql)

Regards,
Andrew Buntine.
[/code]

[aloysius]

thank you so much for your help..but one last problem..i always get this error:

Error Type:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical path was used.
/DSPRMS/create_table2.asp, line 7

i did as told. create one page with a form and a button..my second page's codes look like this:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/conn_DSPRMS.asp" -->
<%
dim conn, conn_DSPRMS, sql

set conn_DSPRMS = server.createObject("ADODB.Connection")
conn_DSPRMS = "DBQ=c:\inetpub\wwwroot\DSPRMS.mdb" & server.MapPath("c:\inetpub\wwwroot\DSPRMS.mdb") & ";"
conn_DSPRMS = conn_DSPRMS & "Driver={Microsoft Access Driver (*.mdb)}"
conn_DSPRMS.open (conn_DSPRMS)

sql = "CREATE TABLE tblTest (intAge integer(3), varName varchar(200))"
conn_DSPRMS.execute (sql)
%>

what seem to be wrong..thank you for your help..i think you are the only hope i have to complete my project

[buntine]

Thats because you are using the MapPath() method where it isnt needed.

'Original
"DBQ=c:\inetpub\wwwroot\DSPRMS.mdb" & server.MapPath("c:\inetpub\wwwroot\DSPRMS.mdb") & ";"

'Change it to this.
"DBQ=c:\inetpub\wwwroot\DSPRMS.mdb;"

'Or try this, if you want
"DBQ=" & Server.MapPath("/DSPRMS.mdb") & ";"

You can use the Server.MapPath() method when you dont know the physical path of the database.

Regards,
Andrew Buntine.

[aloysius]

now they say there is something wrong with line 9 which is:

conn_DSPRMS.open (conn_DSPRMS)

the error is:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'DBQ=c:\inetpub\wwwro'
/DSPRMS/create_table2.asp, line 9

this is after i used your method to change line 7 to:

conn_DSPRMS = "DBQ=c:\inetpub\wwwroot\DSPRMS.mdb;"

[buntine]

Mate, look at what you have done...

Your using the same variable name as an object and a connection string.. Look at the code example i gave you, you will see tha i am using a seperate variable to hold the connection string (conn_string).

Regards.

[aloysius]

thank you for being so patient with me...but now i have a problem on line 12: conn.execute (sql) even after changing the names of the variables. the error stated is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.
/DSPRMS/create_table2.asp, line 12

really need and appreciate your help..thank you so much..

[buntine]

If you havent changed the SQL query since your last post, you will have to consult the microsoft site or look up something in MS Access Help.

Access may treat the CREATE TABLE query differently.

Regards,
Andrew Buntine.

[aloysius]

thanks..i think i'll post the code again..and hope someone else replies..anyway..you got a good site going..keep up the good job..

[buntine]

Yer, it goes alright...

The code i gave you should work fine. Make sure Access treats the CREATE keyword the same as MySQL and Oracle databases.

[moisha]

aloysius,
have you (or someone) figured out how to do it?
I have the same problem
Thanks