Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can you adapt this script so user can create their own table & name?

Status
Not open for further replies.
gtubb

gtubb

Programmer
Jan 15, 2004
24
GB
Hi

I need to create a form that will allow users to add new tables to my Access database.

I can create a table with the script below - but can anyone tell me how to wrap it in a form so the user can choose the name of the table (the fields will be the same in each table)

Many thanks

Gerard

This is the script that creates a table called newtbl:


<% Set conn = server.CreateObject(&quot;ADODB.Connection&quot;)
connectstring = &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; &_
&quot;DBQ=&quot; & Server.MapPath(&quot;config/datasources/schooldoccategories.mdb&quot;)
conn.Open connectString

adSQL = &quot;CREATE TABLE newtbl (Name text (50), Email text (75), Comments text (255), Region text (50))&quot;

Conn.Execute adSQL

Conn.Close
Set Conn = Nothing
%>
 
Sort by date Sort by votes
I should have said user - I'm creating a site for a teacher at my local primary school. He will be the only one creating tables, and populating them (using ASPUpload) with files for the other teachers to use in their classrooms.

Each table name appears as a heading on the page, so other teachers can click on a heading and the records in that table are diplayed. They can then download the documents they need.

Gerard
 
Upvote 0 Downvote
This is basically what you are asking:
(save it as 752320.asp)


<%@ Language=VBScript %>
<%
if request.ServerVariables(&quot;REQUEST_METHOD&quot;) = &quot;POST&quot; then
dim cNewTable
' Get tablename from form and replace all spaces
cNewTable = replace( request.Form(&quot;fNewTable&quot;), &quot; &quot;, &quot;_&quot;)

if cNewTable <> &quot;&quot; then
adSQL = &quot;CREATE TABLE &quot; &_
cNewTable &_
&quot; (Name text (50), Email text (75), Comments text (255), Region text (50))&quot;
response.Write adSQL
response.end
end if
end if


%>
<html>
<form method=&quot;post&quot; action=&quot;752320.asp&quot;>
CREATE NEW TABLE<br>
<br>
Tablename: <input type=text name=fNewTable>
<br>
<input type=submit>
</form>
</html>

<!-- end of file -->



...but you must check if the table already exists. Maybe this article can help you:
[i do nothing with ms access]


but why do you need to create seperate tables? with 1 additional field you can store everything in 1 table....










hth,
Foxbox
 
Upvote 0 Downvote
Thank you for your help. You're going to think this question is really stupid, but I'm new to this.

How do I get your code (which returns &quot;CREATE TABLE ..... (Name text (50), Email text (75), Comments text (255), Region text (50))&quot;) to create the table?

Sorry to be so thick.

Incidentally, I need separate tables because each table name becomes a menu item - the contents of that table are displayed when you click on the name.

And just to show I'm not sitting back waiting for others to do the work, I had managed to cobble together a page that creates a table from a value passed in the URL. I was in the process of trying to work out where to go next:

<% Set conn = server.CreateObject(&quot;ADODB.Connection&quot;)
connectstring = &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; &_
&quot;DBQ=&quot; & Server.MapPath(&quot;config/datasources/schooldoccategories.mdb&quot;)
conn.Open connectString

dim tablename

tablename = Request.QueryString(&quot;Table&quot;)

adSQL = &quot;CREATE TABLE &quot; & tablename & &quot; (Name text (50), Email text (75), Comments text (255), Region text (50))&quot;

Conn.Execute adSQL

Conn.Close
Set Conn = Nothing
%>
 
Upvote 0 Downvote
I've solved it! Thanks Foxbox. The form works with the following script: (now all I have to do is work out how to check for existing tables of the same name):

<%@ Language=VBScript %>
<% Set conn = server.CreateObject(&quot;ADODB.Connection&quot;)
connectstring = &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; &_
&quot;DBQ=&quot; & Server.MapPath(&quot;config/datasources/schooldoccategories.mdb&quot;)
conn.Open connectString
if request.ServerVariables(&quot;REQUEST_METHOD&quot;) = &quot;POST&quot; then
dim cNewTable
' Get tablename from form and replace all spaces
cNewTable = replace( request.Form(&quot;fNewTable&quot;), &quot; &quot;, &quot;_&quot;)

if cNewTable <> &quot;&quot; then
adSQL = &quot;CREATE TABLE &quot; &_
cNewTable &_
&quot; (Name text (50), Email text (75), Comments text (255), Region text (50))&quot;

Conn.Execute adSQL

response.end
end if
end if

Conn.Close
Set Conn = Nothing
%>

%>
<html>
<form method=&quot;post&quot; action=&quot;752320.asp&quot;>
CREATE NEW TABLE<br>
<br>
Tablename: <input type=text name=fNewTable>
<br>
<input type=submit>
</form>
</html>

<!-- end of file -->
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
557
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
475
leifoet
leifoet
DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
487
DiDYRO_
DiDYRO_
Ambientguitar
  • Locked
  • Question
Issue with this not calling the Sub in Classic ASP page
Replies
5
Views
588
MarkSweetland
MarkSweetland
leifoet
  • Locked
  • Question
How to obtain a different table title on a continuation page ?
Replies
3
Views
341
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top