Create an Access DB and DSN with ASP? 5

dflanagan · Nov 13, 2002

hello!
I was wondering:
Can you use ASP to create an Access database? If so, what code would you use?

Also, if you cannot create a database, can I copy an existing one to a new location, and then make a DSN of it using ASP? I am creating an online service that I hope will have alot of customers. I do not think it would be good for them all to share one database and have hundreds of tables in it. I know I should probably use SQL2000, but... one step at a time! I am small yet!

Thanks!

tty0 · Nov 13, 2002

There is a way you can create a new database from ASP code but if you are using the same format for each database then maybe the best road to go down is to simply copy an empty database across with a new name so that you already have the tables and fields set up.

Try changing the following round to your needs:

dim fileOBJ
set fileOBJ=Server.CreateObject("Scripting.FileSystemObject&quot

fileOBJ.CopyFile "c:\WINNT\INETPUB\database.mdb","c:\WINNT\INETPUB\database2.mdb"
set fileOBJ=nothing

dflanagan · Nov 13, 2002

yeah, that is what I have come up with in the mean time. Now with that, I have turned to using DSN-LESS connections to the access databases. Do you know of a downfall to doing this? I can copy the database to a new location, and in the same page, add values to a table to give it its unique identity, then use the DNS-LESS connection method. I just want to know if this is ok before I plow into the code so that it does all of this automatically.

Thanks for the response!

tty0 · Nov 13, 2002

The DSN connections reference the registry when connnecting so they are slightly slower. However you cannot notice the difference until there are more than 10 concurrent connections to the database. Easiest solution to your needs would be to make two files for the connections and whenever you need dsnless simply include the dsnless.asp file or otherwise include the dsn.asp file. Or whatever naming convention you are using.

That is the way I manage my connections in our search engine and it works great.

cheers

chris
chris@techsupportuk.com

dflanagan · Nov 13, 2002

cool!
So are you saying that there IS a way to make DSN connections with ASP then? If so, how? (for future reference)

Thanks!

tty0 · Nov 13, 2002

Hmm not sure if the last post went through so here goes again just incase:

DSN-less is faster than DSN as it does not reference the registry every time it is used unlike the DSN connection. However there is no noticable difference until there are more than 10 concurrent connections. There was an argument that DSN was faster at one time but tests show them to be 13 - 16% slower.

To manage this easier use include files, one fot he DSN and one for DSN-less so it will save you a lot of coding. Plus if you need to change the connection properties and you have the code in lots of seperate pages it is easier to change one include than all.

cheers
chris
chris@techsupportuk.com

tty0 · Nov 13, 2002

Yup there is but what part? Are you asking how to create a DSN on the computer using ASP or how to connect by DSN mate?

gmagerr · Nov 13, 2002

Here's something i found and modified, Is this what you're looking to do? You can rename the pages anything you want, just make sure the form posts to the second page and the second page redirects to the third page.

'-----------------------------------------------------------
Name this page CreateDatabase.asp
'-----------------------------------------------------------

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<DIV ALIGN=Center>
<H2>Create a new database using an existing database as a template.</H2>
<FORM ACTION=CreatingDatabasesInAsp.asp METHOD=Post NAME=frmDatabase>
Choose a name for your new database:<BR>
<INPUT TYPE=Text NAME=DatabaseNew>
<BR>
<INPUT TYPE=Button NAME=btnSubmit VALUE=Submit>
<INPUT TYPE=Reset VALUE=Reset><BR>
</FORM>
</DIV>

<SCRIPT LANGUAGE=VBScript>
Sub btnSubmit_OnClick()
'Validate the data in the textbox
If Len(frmDatabase.DatabaseNew.Value) = 0 Then
Alert "You must enter a name for the new database"
frmDatabase.DatabaseNew.Focus
Exit Sub
End If
Call frmDatabase.Submit()
End Sub
</SCRIPT>

</body>
</html>

'-----------------------------------------------------------
Name this page CreatingDatabasesInAsp.asp
'-----------------------------------------------------------

<%
'************************************************************************************
' If you're creating cookie-cutter databases,
' generate a template with table structure and no data, and simply copy it:
'************************************************************************************

DatabaseNew = Request.Form("DatabaseNew&quot

targetDB = "c:\inetpub\

http://wwwroot\Magerr

Media\database\" & DatabaseNew & ".mdb"
sourceDB = "c:\inetpub\

http://wwwroot\Magerr

Media\database\Telmon.mdb"

set fso = Server.CreateObject("Scripting.FileSystemObject&quot

fso.CopyFile sourceDB, targetDB, true
set fso = nothing
set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetDB
Response.Redirect "DatabaseSuccess.asp"
' ...

'************************************************************************************
' Here is code that will create an empty Access database from ASP:
'************************************************************************************

'newDB = "c:\inetpub\

http://wwwroot\Telmon\database\new.mdb"

'Set cat = Server.CreateObject("ADOX.Catalog&quot

'cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newDB
'Set conn = Server.CreateObject("ADODB.Connection&quot

'conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newDB
' create tables, etc...
%>

'-----------------------------------------------------------
Finally name this page DatabaseSuccess.asp
'-----------------------------------------------------------

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<DIV ALIGN=Center><H2>Database Created... Cool huh?</H2></DIV>
</body>
</html>

I beleive there is a section on the second page that'll allow you to create a blank database (it's remmed out) Hope this helps.

dflanagan · Nov 14, 2002

WOW!
Nice post man! What you showed above is what I am doing. I was actually interested in how to MAKE a DSN with ASP. Is that possible? I know how to connect to a DSN, and how to connect to and Access db without a DSN, just wondering HOW to CREATE a DSN for an access db if possible.

Thanks for all of the input!

TonyU · Nov 14, 2002

[tt] gmagerr you get a

from me also. Great future reference...
^<%=Tony%>

gmagerr · Nov 14, 2002

WOW!! thanks for the stars guys... I'm going to search around and see if i can find anything on creating dsn's. i do know however, if you create a blank text file on your desktop, and rename it to whatever.udl you can do database connectivity stuff from there. anyway i'll look around and see what i can come up with, i think that would be a very cool feature to have in the asp community.

dflanagan · Nov 14, 2002

You are welcome!

That was a quality post!

gmagerr · Nov 14, 2002

Ok, i think i may have come up with a solution... first of all, can you use a file dsn instead of a system dsn? if so, check this out. First of all you need to create the file dsn. it's basically a text file but instead of a .txt file extension, it uses a .dsn file extension. So you can create a blank whatever.txt file and reneme it whatever.dsn ok, with that said, here's what goes in the whatever.txt file for the connection

Create the DSN
Create a file with the same name as your database ending in DSN rather than MDB (for this example, we'll call the file database.dsn)
'============================
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
ReadOnly=0
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=2
'============================
again, just cut and paste the information to a text file. no modifications. But remember to change the extension to .dsn

Ok, now with this information, you could create this file in asp with the FileSystemObject correct?
Then here's an asp page to pull data using the new dsn

'-----------------------------------------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Set cnDatabase = Nothing
' Create a command object. This object serves to run our queries
Set cnDatabase = Server.CreateObject("ADODB.Connection&quot

'The Connection string looks for a database file called database
ConnStr = "filedsn=" & Server.MapPath("database.dsn&quot

ConnStr = ConnStr & ";DBQ=" & Server.MapPath("database.mdb&quot

cnDatabase.Open ConnStr
' Retrieve the results in a recordset object
strSQL = "SELECT * FROM [Employees] WHERE [FirstName] = 'Gene'"
Set rsDatabase = Server.CreateObject("ADODB.Recordset&quot

rsDatabase.Open strSQL, cnDatabase, 3, 3
'Now we have a record set which is read/write
Do While Not rsDatabase.EOF
%>
HERE YOU PLACE YOUR STANDARD OUTPUT
<b>This is a field: <%= rsDatabase("FirstName&quot

%> <%= rsDatabase("LastName&quot

%></b>
<%
rsDatabase.MoveNext
Loop
'Make sure you clean up ! Not closing properly will ruin your whole day!
rsDatabase.Close
Set rdDatabase = Nothing
cnDatabase.Close
Set cnDatabase = Nothing
%>

'-----------------------------------------------------------
Notice, both my dsn and database are named database, i think you can change this to whatever you want, also notice they are both in the root directory, you might want to change that also. So can someone try to create some code that creates the .dsn file? i'll work on it too, but i'm a little burned out right now

Oh if you're going to try this code the way it is, make sure your database has a table with FirstName and LastName in it...

dflanagan · Nov 14, 2002

WOW!!!

You are THE MAN!!

gmagerr · Nov 14, 2002

God I love ASP... thanks again dflanagan

tty0 · Nov 14, 2002

The way I use to create DSN's on the fly is to pass details to a registered DLL on our server and let it create it. I haven;t seen anything in ASP before to create the DSN as it needs to access the servers registry.

'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

may1hem · Dec 26, 2002

Noobie question here:

Why would you want to create a DB on-the-fly and why would you want to create a DSN on-the-fly?

Thanks,

May

tty0 · Dec 27, 2002

There are many different reasons for making DSN's and/or DB's on the fly, one of them is due to user friendliness which was mine.

I had a brief to make a search engine and that each type of business in the site search would use a different database but in each database the fields would be the same so basically when you wanted to search one specific one all you had to do was put the database name behind an option button and pass the name across. then the engine would use the same functions to handle the data.

So when a new business type was added to the site the software added a new DSN, a new connection file and finally a new DB named by the user. All that was used is the name fo the databsae, the rest was handled in the code so there was a simple text box and submit button.

Which ended up with a user with little to no computer knowledge being able to add a DSN, DB and make an ASP dbconnection file with one word and a click.

And finally the reason for diff DB's and not just tables was because of size and maintainance.

Cheers

oh and thanks for the stars before, didt notice them till now! 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

lottastuff · Dec 27, 2002

This "How to create a DSN ont the fly" is realy interesting and tty0 states an excelent example of where it would make sense to use the technique. However, dflanagan's original post leads me to believe a serious misconception is at work here. ..."I do not think it would be good for them all to share one database and have hundreds of tables in it."...

Generally speaking, when a database is used to store information that is different for each user, the structure of the data is the same (hence allowing the use of a small number of tables that inter-relate, but the content of the fields within the tables is what varies and can be associated with a particular user ("owned by" if you like).

This is generally accomplished by requiring each user to register (assigning them a unique identifier kept in some sort of accounts table, maybe with their password), and requiring that they logon each time they start a session.

Following the logon, the unique id field is used as part of any "where" clause to isolate rows specific to that individual from all the other rows in the same table(s) that are for other users. The unique id is typically handed around from page to page in a session variable.

Finally, this is all pretty basic DB design stuff and does not require you to use SQL server to implement. When a DB is accessed via an ASP page, there is only one user (typically IUSER_nodename), so unless your site gets a lot of simultaneous users, there is very little concern about access to a MS Access DB.

dilettante · Dec 28, 2002

The main reason to use system DSNs instead of DSN-less connections is security. Unless you feel really sure nobody can figure out how to make your server cough up the raw ASP, you are playing with fire. Of course there are tricks to obfuscate or hide passwords for connection strings too I guess.

Actually Access databases scale poorly in an ASP environment because of its highly transactional nature. Unless you do something even worse (like cache open connections in the session or application objects) you are doing one heck of a lot of file opening and closing, fishing out the metadata tables, the coarse index tables, and other overhead related to opening an MDB.

Concern about the cost of accessing a system DSN seems really misplaced.

That said, most of us still use Access all the time because Access is such an easy thing to use. But I've gotten really burned a number of times and found that using MSDE instead of Jet databases made most of my problems disappear. If loads really ramp up it is then simple to move up to SQL Server, since for MSDE you've already written everything in "SQL Server-ese" (just add money).

For desktop applications where the users just sit there with the database open all day I've found that MSDE is great up until you pass the "throttle point" of 10 users. In such a scenario Access isn't too bad up to a couple hundred users if they aren't really active. What kills Access is network performance, because so much data has to be dragged and pushed over the wire from/to the file server. But for ASP applications things are almost turned right around. I believe this is because you just don't have that open/close overhead you have with Jet.

One thing that has changed in recent years though is CHEAP MEMORY. Since I seldom have a web server with under 256MB in it now (and generally more) I suspect that disk caching and other memory-related factors have reduced Access performance issues impressively for smaller databases. This is a far cry from running the same sorts of applications under NT 4 in a box with 32MB to 64MB.

I still rely on MSDE for large databases though, and I find that when I hit about 70 to 80 pretty active ASP users things slow down. Moving the database to a separate server seems to get me going again up to around 150 active users (I'm defining "active" as one page-hit every 2 seconds). A lot depends on your server hardware too of course.

Sooner or later you wham into the throttle in MSDE though, and moving to SQL Server smooths the road right out. I haven't been hitting performance bottlenecks I can trace to the database activity when I use SQL Server. The whole performance measurement thing is quite tiring though, I'd rather write code.

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!

Create an Access DB and DSN with ASP? 5

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

Technical User

MIS

Technical User

Technical User

MIS

Technical User

MIS

Technical User

Programmer

Programmer

Programmer

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor