Moving ms access driven site from sql server to jabry.com - connection string problems

Question

Hi,

I've done a fair bit of mysql php programming but am pretty new to asp/access etc.

This will either be incredibly easy or impossible!

SUMMARY:

I'm trying to transfer a web application from a corporate network to an external web host for testing and don't know how to connect the pages to the database on the new server.

BACKGROUND:

I've inherited a (ms access) database driven website from a corporate server and have been asked to pull it apart and document it so that there's no single point of failure issue with it (the original programmer disappeared long ago)

It was hosted on an ms sql server on the corporate network.

I've obtained some free hosting for access driven websites at www.jabry.com and hoped to transfer the whole application to there and use that as a test bed.

I got a copy of the .mdb file and uploaded it to jabry. My question is what connection should i use to the new database?

THIS IS THE OLD CONNECTION INSTRUCTION :

dim objConn
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=UKNTHSQL900;UID=****User;PWD=***able8; DATABASE=PropertyRegister"

JABRY RECOMMEND USING :

Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
strConnection = "DRIVER=Microsoft Access Driver(*.mdb);DBQ=" & Server.MapPath("/USERNAME/db/yourdatabase.mdb")
oConn.Open(strConnection)

I've tried various permutations to get this to work, but after a couple of hours(!) thought I'd better get some help.

Can I use a dsnless connection when a password is required? Are the two server types completely incompatible? Please help!

Answer 1

This site should help: http://www.connectionstrings.com/access.

  dbfile=Server.MapPath("..\YourDB.mdb")
  Set objConn=Server.CreateObject("ADODB.Connection")

  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & dbfile & ";Jet OLEDB:Database Password=PW"

The "..\" part depends, as you may imagine, on where you are running the code and where the database resides.

EDIT re Comment: You can also use ADO to enumerate the tables and queries held in the database.

   ''cn = Your connection
   Set rs = cn.OpenSchema(20, Array(Empty, Empty, Empty))

   Do While Not rs.EOF
      '' This is vbscript / vba, as per comments, 
      '' change to rs("Table_Type") for ASP ...
      If rs!TABLE_TYPE <> "View" Then
         '' ... and this to rs("TABLE_NAME") ...
         s = s & rs!TABLE_NAME & vbCrLf
      End If
      rs.MoveNext
   Loop

   rs.Close
   Set rs = Nothing

   '' ... and this to Response.Write s
   MsgBox s

More info: http://msdn.microsoft.com/en-us/library/aa165325%28office.10%29.aspx

Answer 2

How did you obtain the .mdb file? If you exported from an SQL Server database to an Access database file, it may or may not work depending on the features used in the SQL Server database. If it was used just for "dumb" storage, the Access database file can do that, but if it used features like stored procedures, user defined functions or triggers, the Access database file can't replicate that.

If the Access database has a password set, you have to specify that in the connection string. The user name that you specify is "Admin", so the connection string should look something like:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Server.MapPath("/YourUserName/db/TheDatabaseFileName.mdb") & ";Uid=Admin;Pwd=ThePassword;"

Of course you have to substitute the YourUserName, TheDatabaseFileName and ThePassword parts with the actual values. You also have to have uploaded the database into the db folder on the server, which is set up with the proper file permissions for the web application to be able to write to the database file.

What the web hotel has recommended is an ODBC driver, but I would rather use a JET driver if possible. You can find a lot of examples of connecion strings for Access here: http://www.connectionstrings.com/access