SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2000
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP and Databases

    I have a few beginner ASP and Database questions.

    If you setup a DSN can you later get the Access file back on your computer make changes and put it back without changing DSN settings again?
    (I sure hope that made sence).

    Also,
    If I have the option, should I use MSSQL instead of Access. If it's better, how do I setup the database. I know how to create a database with Access but do I need software to create a MSSQL database?
    Thanks!

  2. #2
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: ASP and Databases

    Originally posted by sammy182
    I have a few beginner ASP and Database questions.

    If you setup a DSN can you later get the Access file back on your computer make changes and put it back without changing DSN settings again?
    (I sure hope that made sence).
    Assuming you don't change the name of the file, there should be no problem.

    And assuming you haven't put anything into your database that's incompatible with your current DSN settings (don't know about ASP, but an example would be long text field retrieval in ColdFusion).


    Also,
    If I have the option, should I use MSSQL instead of Access. If it's better, how do I setup the database. I know how to create a database with Access but do I need software to create a MSSQL database?
    Thanks!
    You definitely need software to create an MS SQL database And unless your hosting provider already runs it, it's very expensive.

    If you're just starting out, your database isn't too big and you don't have too many users, then stay with Access for now. Sounds like you know it. Later on, the upgrade path from Access to MS SQL, while not exactly trouble free, is fairly easy.

    <edit>
    One thing I forgot to point out - you may be able to use Access's inbuilt replication services if you want to make changes to your live database on another machine. However you may also run into MDAC compatibility problems if you're using an old version of Access and a new version of Windows.
    </edit>
    Last edited by hillsy; Dec 3, 2001 at 03:56.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would actually recommend fiddling with a 120 day eval copy of MS SQL Server 2000. The SQL syntax is similar to that used in Access, and databases are very easy to create.

    You can either use Enterprise Manager (a GUI), or Query Analyser to create them manually. I would recommend using enterprise manager to learn db structures, then use the in-build query analyser help to learn the SQL ANSI language syntax
    Last edited by Mytch2001; Dec 3, 2001 at 06:55.
    Get ConMan and run your own web site!

    Want free programming eBooks? http://www.devarticles.com/ebooks.php

  4. #4
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Or you could use SQL Structure Creator Pro which is essentially a true GUI database creator which then outputs all the code you need...

    Instead of it taking 3 hours to create a proper DB in SQL Server, it takes 15 minutes and includes all relations and so on. It saves us so much money
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2000
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do these tools create a database file, or the SQL code to stick in an ASP file to create the database?

    Is MSSQL like Access where there is a file, or like MySQL where there isn't really a file?

  6. #6
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Access is a nice little program.

    MySQL is a fairly decent little program.

    SQL Server is a server.

    SQL Server allows you to import .sql files which it can then execute (just as if you'd typed in the 2000 lines of code) to create your database system for you.

    ... Does taht answer your question?
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2000
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think so...
    The host says is supports:
    Access
    DBase
    Excel
    Paradox
    Text
    Visual FoxPro
    MSSQL


    And there is no actual file for MSSQL? But the others there are?

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    There are no "files" for SQL Server, no, but you can "export" your database for use on other servers, such as webhosting, which creates files which are then "melded" into your website. (if my understanding is correct)
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A database resides in a database 'device' (which is ultimately a file-system file or a raw device) on MS SQL (and Sybase). A database must reside on one or more devices. You can split up indexes, logsegment(s), and data areas into different devices. However, you can not just 'copy' the database devices to your hard drive and expect it to work.

    If you do wish to have a copy of the database on your local machine to work with (copied from the server) instruct the server to make a database dump (like Jeremy suggested) and then load it into your home copy of MS SQL Server.

    Access is like a 'database in a single file' wheras MS SQL Server requires a server process running in the background to catch all of the SQL and pass it on to the database (sort of).

  10. #10
    SitePoint Zealot
    Join Date
    Oct 2000
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok...
    I think I am getting it. So MSSQL is NOT some file that you can point with a ODBC driver...
    It is it's own large, expensive server thing....

    I think I'll just use Access..

  11. #11
    SitePoint Zealot
    Join Date
    Oct 1999
    Location
    France
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So MSSQL is NOT some file that you can point with a ODBC driver...
    Well, yes, you can point to it with an ODBC driver (tho' it's better to use ADO/DSNLess connections). But it's not simply a file that you can shuffle backwards and forwards between your local PC and the server. If you need to do this, you will have to use Access. You can also use Access as a front end to manage a remote SQL Server db though, which might interest you.

    In short, SQL Server is far more robust, faster, and more scalable than Access. If you are expecting lots of traffic and a high volume of data, it's a better choice. But if you're looking at a small database, few users, and you are not already experienced with SQL Server, Access should be fine. If you change your mind later, Access has an upsizing wizard that makes it easy to import an Access DB to SQL server.

    HTH
    --
    Veronica Yuill
    Archetype IT

  12. #12
    SitePoint Zealot
    Join Date
    Oct 2000
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, how would you setup a MSSQL database on a server then?

  13. #13
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    There's an "export"-type utility which allows you to package up the database system as files, for installation on another SQL Server. They are useless on their own though
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  14. #14
    SitePoint Zealot
    Join Date
    Oct 1999
    Location
    France
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, how would you setup a MSSQL database on a server then?
    Do you mean using Access? With Access 2K, you can create an "Access Data Project". This uses ASO to connect to a remote SQL Server database (obviously you need access rights to the db you are attaching to). On initial setup, you have to specify server name, user name, password, database etc. It works a little bit like attaching ODBC data sources to an Access DB, except that you can do more with the tables (most of what you can do using enterprise Manager in fact). Once you have successfully opened a connection to the db, you can use Access to create tables, views, stored procedures etc. (the interface looks a little different from maintaining normal Access tables). You can also create Access forms to maintain the data directly. If you have Access 2K, check out the help under "Access Data Project".

    HTH
    --
    Veronica Yuill
    Archetype IT

  15. #15
    SitePoint Zealot
    Join Date
    Oct 1999
    Location
    France
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was a typo -- I meant ADO, not ASO!
    --
    Veronica Yuill
    Archetype IT

  16. #16
    I have an opinion...
    Join Date
    Sep 2001
    Location
    Barrie, Ontario
    Posts
    324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All the following DBMSs use files for DBs.

    Access
    SQL Server
    MySQL
    PostreSQL
    Oracle
    DB2
    dBase

    The question is what form of file. Access is the only one that really has a single "file" for the db, but believe me, they ALL use files. (Although I would LOVE to see MS SQL load my 14 million records into RAM instead of a file.)

    Incidentally, MySQL is a server also, Jeremy. )
    Egotist: A person more interested in himself than in me.
    KodeKrash - Eidix - Barrie LUG

  17. #17
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    They may well use files, however they are not file-based. You couldn't, for instance, copy 3-10 files and just recreate your SQL Server database elsewhere. The same is true of Oracle, DB2, Interbase, etc.

    MySQL a server? You learn something new every day If you read back, I didn't rule it out as a server since I wasn't sure
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  18. #18
    SitePoint Zealot
    Join Date
    Oct 1999
    Location
    France
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You couldn't, for instance, copy 3-10 files and just recreate your SQL Server database elsewhere
    Nitpicking here ... but actually you can move a SQL Server DB by running a stored procedure to detach it from the existing server, copying the necessary files, and attaching them to another server. It's not exactly standard practice though!
    --
    Veronica Yuill
    Archetype IT

  19. #19
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, but that attaching/retaching is key, it's not simply: copy/paste Let's roll!
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  20. #20
    Apache Expert i_like_php's Avatar
    Join Date
    Nov 2001
    Location
    Dallas, Texas
    Posts
    1,342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm new with database connections k, but lets say you create a new .mdb how do you add /delete records or tables from it..
    i love php

  21. #21
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Here's a basic connection code:

    Code:
    	DBPath =  Server.MapPath( "database\rentals.mdb" )
    	Set Con = Server.CreateObject( "ADODB.Connection" )
    	Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
    And to execute SQL (which I assume you know) do:

    Code:
    	sql="SELECT * FROM tablename"
    	Con.Execute(sql)
    Then we close the connection object:

    Code:
    	Con.Close
    	Set Con=Nothing
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •