Move ms access managed site from sql server to jabry.com - connection string issues
I've done some honest mysql php programming but fairly new to asp / access etc.
It will be incredibly easy or impossible!
SUMMARY:
I am trying to push a web application from a corporate network to an external web host for testing and have no idea how to connect pages to a database on a new server.
BACKGROUND:
I inherited a database based website (ms access) from a corporate server and was asked to split it up and document it so it doesn't have any crash issues (the original programmer was long gone)
It was hosted on a ms sql server on the corporate network.
I got free hosting to access websites at www.jabry.com and hoped to transfer the entire application there and use it as a test bed.
I got a copy of the .mdb file and uploaded it to jabry. My question is which connection should I use for the new database?
THIS IS SENIOR CONNECTION INSTRUCTIONS:
dim objConn
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=UKNTHSQL900;UID=****User;PWD=***able8; DATABASE=PropertyRegister"
JABRY RECOMMENDS 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 tried various permutations to get this to work, but after a couple of hours (!) Thought I had better help.
Can a dsnless connection be used when a password is required? Are these two types of servers completely incompatible? Please, help!
source to share
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 can imagine, on where you are using the code and where the database is located.
EDIT re Comment: You can also use ADO to list tables and queries stored 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 information: http://msdn.microsoft.com/en-us/library/aa165325%28office.10%29.aspx
source to share
How did you get the file .mdb
? If you exported from a SQL Server database to an Access database file, this may or may not work depending on the functionality used in the SQL Server database. If it was only used for silent storage, the Access database file can do so, but if it uses features such as stored procedures, UDFs, or triggers, the Access database file cannot replicate this.
If your Access database has a set of passwords, you must specify this in the connection string. The username you provide is "Administrator", so your connection string should look something like this:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Server.MapPath("/YourUserName/db/TheDatabaseFileName.mdb") & ";Uid=Admin;Pwd=ThePassword;"
Of course, you have to substitute the parts YourUserName
, TheDatabaseFileName
and ThePassword
for the actual values. You must also upload the database to a folder db
on the server that is configured with the correct web application access permissions to write to the database file.
What the web hotel recommended is an ODBC driver, but I would rather use a JET driver if possible. You can find many examples of connesion strings for Access here: http://www.connectionstrings.com/access
source to share