Open connection to MS Access Database

[hotflash]

Hi Crocrew et. All,

So far everything is working fine however, I want more security by adding a Username: Admin and Password: Test to my MS Access Database. I have 2 separate databases on my server and used two different methods to open a connection to my databases. Like I said, everything works fine without the username and password. I have searched around the internet and tried different sernarios but they don't seem to work anymore. Can you help please?

1st Method: To connect to the MOPsCentral.mdb database.

Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("MOPsCentral.mdb")

2nd Method: To connect to the PTSystem.mdb database.

Function GetConnection()
dim Conn: Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.open "Data Source=" & Server.MapPath("PTSystem.mdb")
set GetConnection = Conn
End function

[CroCrew]

Hello hotflash,

Someone that is more proficient in Microsoft Access needs to step up and help out on this one. I am a Microsoft SQL Server brat but I know just the right amount to be dangerous in Access. But, I will try to give it a shot.

What I think you are trying to do is lockdown your Access database so that a password has to be passed in via the connection string to gain access to the database.

Solution:
Start Microsoft Access. Close all open databases. Open your database by selecting [File] option from to top toolbar. From the dropdown subset menu select the [Open] option. An Open window dialog will pop up. Browse to your database within this new open window dialog (but do not open it). After you have found your database highlight it by selecting it. At the bottom right of the current open window dialog your will see the [Open] button. Within and to the right of the [Open] button your will notice a [down arrow]. Select this [down arrow] to see an open subset menu. Select the [Open Exclusive] option.

Now that we have our database opened exclusively we can add a password to it. Select the option [Tools] from the top toolbar. From the subset menu hover over the option [Security] and select the option [Set Database Password]. A new set database password window will pop up with two textboxes. For this example in both textboxes enter “happy” (lower case and without the quotes) and click the [OK] button. Close Microsoft Access now completely now.

We are done with the database. Let’s look at our coed now. Try this code to access your database now:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Relative path to your Access database ") & "; Jet OLEDB:Database Password=happy"
'    "DRIVER={Microsoft Access Driver (*.mdb)}; Database Password=happy; DBQ=" & Server.MapPath("Database/Database.mdb") 
    Set rsList = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM YourTable"
    rsList.CursorType = 1
    rsList.LockType = 3
    rsList.Open SQL, Conn
%>
 
 

If you’re really looking to be more secure then I would suggest to upsize your Access database to something like Microsoft SQL Server.

Hope this helped you out~

[hotflash]

Hi CroCrew,

Sorry that I missed one more portion on the second part of the Open connection. I have to use this type of strange format because I have to attach the file along with the record submission as well. I got the source code from
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=8225&lngWId=4 that's why i have to stick with their format for now.

Thanks and please help.


Function OpenUploadRS()
Dim RS : Set RS = CreateObject("ADODB.Recordset")

'Open dynamic recordset, table Upload
RS.Open "PTSProjects", GetConnection, 2, 2

Set OpenUploadRS = RS
End Function

Function GetConnection()
dim Conn: Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.open "Data Source=" & Server.MapPath("PTSystem.mdb")
set GetConnection = Conn
End function

[CroCrew]

Hello hotflash,

Can you do this (that is after adding a password on your database as in the example above)?

<%
    Function GetConnection()
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Relative path to your Access database ") & "; Jet OLEDB:Database Password=happy"
        set GetConnection = Conn
    End function
%>
 
 

[hotflash]

Hi CroCrew,

YOU ARE THE BEST. The 2 recommendations that you provided above worked fine. I have one more format that I got and wonder if you can help as well. Thanks for your help.

<%
set objconn=server.createobject("adodb.connection")
connpath="DBQ=" & server.mappath("PTSystem.mdb")
objconn.Open "DRIVER={Microsoft Access Driver (*.mdb)}" & connpath
set objrs=objconn.execute("select * from PTSProjects")
%>

[CroCrew]

Hello hotflash,

Can you give us a bit more information on what you are trying to do and what errors if any that you are getting?


Thanks~

[hotflash]

Hi CroCrew,

I want to put a password protect on my database like the previous 2 examples you gave me. I am getting Internal Error. Since the database is password protected, how can you modify the code below to make it work with a Database Password? Thanks for your outstanding support.

<%
set objconn=server.createobject("adodb.connection")
connpath="DBQ=" & server.mappath("PTSystem.mdb")
objconn.Open "DRIVER={Microsoft Access Driver (*.mdb)}" & connpath
set objrs=objconn.execute("select * from PTSProjects")
%>

[CroCrew]

Hello hotflash,

Try this code over that code:

<%
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Relative path to your Access database ") & "; Jet OLEDB:Database Password=happy"
        Set objrs = Server.CreateObject("ADODB.Recordset")
        SQL = "select * from PTSProjects"
        objrs.CursorType = 1
        objrs.LockType = 3
        objrs.Open SQL, Conn
%>
 
 

[hotflash]

Hi CroCrew,

Thanks for your help but I still can't export the table to Excel after I put the password protect on the database. Below is the ORIGINAL code that I used and worked fine before I put the password on the Database.

I got this code from the Internet. Thanks.

<%
Response.ContentType = "application/vnd.ms-excel"
%>

<%
set objconn=server.createobject("adodb.connection")
connpath="DBQ=" & server.mappath("PTSystem.mdb")
objconn.Open "DRIVER={Microsoft Access Driver (*.mdb)}" & connpath
set objrs=objconn.execute("select * from PTSProjects")
%>
<TABLE BORDER=1>
<TR>
<%
'Loop through each Field, printing out the Field Names
For i = 0 to objrs.fields.count - 1
%>

<TD><% = objrs(i).name %></TD>
<% next %>
</TR>
<%
'Loop through rows, displaying each field
while not objrs.eof
%>
<TR>
<% For i = 0 to objrs.fields.count - 1
%>
<TD VALIGN=TOP><% = objrs(i) %></TD>
<% Next %>

</TR>
<%
objrs.MoveNext

wend

objrs.Close
objconn.close
%>

[CroCrew]

Hello hotflash,

Here is your code. Copy and paste it over yours and it should work.

<%
Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("PTSystem.mdb") & "; Jet OLEDB:Database Password=happy"
    Set myRS = Server.CreateObject("ADODB.Recordset")
    SQL = "select * from PTSProjects"
    myRS.CursorType = 1
    myRS.LockType = 3
    myRS.Open SQL, Conn
%>
<html>
    <head>
        <title>Example</title>
    </head>
    <body>
        <%Response.ContentType = "application/vnd.ms-excel"%>
        <table border="1">
            <tr>
                <%For Each Field In myRS.Fields%>
                    <td><%Response.Write(Field.name)%></td>
                <%Next%>
            </tr>
            <%Do Until (myRS.EOF)%>
                <tr>
                    <%For i = 0 to myRS.fields.count - 1%>
                        <td><%Response.Write(myRS(i))%></td>
                    <%Next%>
                </tr>
                <%myRS.MoveNext%>
            <%Loop%>
        </table>
    </body>
</html>
 

Hope this helps~

[hotflash]

Hi Master CroCrew,

You are TRULY A HERO on this forum. Everything works FINE. Questions for you.

1. How can I remove let's say a field called WorkDescription when it is
EXPORT to a file or view?

2. Since you are so good at the ASP stuff, do you know of any script out there that will allow you to generate reports such as:

a. Number of projects opened per month for example 1/1/2008 to
1/31/2008?
b. Display the report in sometype of charts?

Thanks once again for your outstanding support.

[CroCrew]

Hello hotflash,

I would not suggest “removing” the record from your database. What I would do if it were me is again; create another field within your database (yes/no) that you can set to know if the data has been “EXPORT”ed to not show up again if the report were to be ran again. I don’t like to “Delete” data till I know for 100% I don’t ever need it again (and that is rare). So, where you’re building your report check to see if this new flag (yes/no) has been set.

As for charting…

I have only used two solutions to do my charting and both I had to pay for.

http://www.simplechart.net/AspChart.aspx?id=40

http://www.dundas.com/

Let me know if I can provide more help~

[hotflash]

Hi CroCrew,

Thanks for your information. I have noticed that most of your code has the following:

myRS.CursorType = 1
myRS.LockType = 3

You have a trick where let's say, If I have a project open and getting ready to make the update, and whoever comes behind me can't update it until I am done and out of the record? Thanks.

[CroCrew]

Hello hotflash,

A trick? Well, not an easy one. You could add fields to your database that you could check to see if someone has the record checked out.

What comes to mind are fields like:
{Name of Field}, {Data Type}
IsCheckedOut, (Yes/No)
CheckedOutBy, Text
TimeCheckedOut, Date/Time

This way you can know if the recorded is being used and buy whom it is used by and when they checked it out.

Hope that starts the thought process~

[hotflash]

Hi CroCrew,

As always, when it comes to ASP, I think you are the best of all bests. This sounds very interesting. I can't believe we can do this using ASP. We used to use a MS Excess as file sharing on the server. Each time when there is a user opens the file, and a second person comes in, he/she can't save to the file because of a first user already had it opened either to view or edit.

I have moved it to a website, I created a search feature like: A (search for file A), and B (search for file B). Let's say when file A is searched, it will display the file A name, and you can either View or Edit. The file for this is DisplayFile.asp.

What do I need to put in the DisplayFile.asp, or EditFile.asp to make it lock the file so others won't be able to view or update it? Thanks for your help.

[CroCrew]

Hello hotflash,

It seems like the original question you had has been resolved with the help of this site.

Can you post a new question so that the “title” reflects what your asking help for?

This helps in keeping the thread short and makes for easier searching on this site for *like* answers/solutions.

Thanks~