Hello,<br><br>I have an Access2000 database that is secured by a one username and a password. I am using both DAO and ADO data access objects to access this database in one VB6 application. I have no problem writing code to allow the DAO access facility to access the secured database. I have spent many frustrating hours attempting to get the ADO object to do the same thing, can some one please let me know what I should do to resolve this issue, possibly with the help of some sample code if it is not too much trouble.<br><br>Regards
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!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ADO Data Access
- Thread starter mans
- Start date
Here is some code that uses ADO to access an MS Access table. I hope you can get something from this code.<br><br> ' ado code for UPC table connection<br> Dim strSQL As String<br> Dim cnnConnection As ADODB.Connection<br> Dim rstUPC As ADODB.Recordset<br> Set cnnConnection = New Connection<br> strSQL = "select * from tblUPC"<br> cnnConnection.ConnectionString = itsConnectionString<br> cnnConnection.Open<br> Set rstUPC = GetRecordset(cnnConnection, strSQL)<br><br>and in a module I have:<br><br>Public Function GetRecordset(cnnConnection As ADODB.Connection, strQry As String) As ADODB.Recordset<br> <br> Dim rstUPC As Recordset<br> Set rstUPC = New Recordset<br> <br> rstUPC.CursorType = adOpenKeyset<br> rstUPC.LockType = adLockOptimistic<br> rstUPC.CursorLocation = adUseClient<br> rstUPC.Source = strQry<br> Set rstUPC.ActiveConnection = cnnConnection<br> rstUPC.Open<br> Set GetRecordset = rstUPC<br> <br>End Function<br><br>I learned this from the Waite Group's Visual Basic 6 Superbible. It is an excellent book.<br> <p>Wally Wojciechowski<br><a href=mailto:wwojo@wilsonpet.com>wwojo@wilsonpet.com</a><br><a href= > </a><br>
Well, wallyw has it, but I'm gonna post some code to just because I'm bored...<br>-----------------------------------------------<br><br>dim db as new adodb.database<br>dim rs as new adodb.recordset<br>dim sql as string<br><br>db.Open "driver=Microsoft Access Driver (*.mdb); dbq=" & App.path & "\databasename.mdb"<br><br>sql = "select * from tablename"<br><br>rs.Open sql, db, adOpenKeyset, adLockPessimistic<br><br>-----------------------------------------------------------<br>Note that the "db.open" line is all one line, including app.path beneath it.<br><br>After that you can use the rs object as like a pretty much like dao through code (i.e. movenext, movefirst, movelast, delete, addnew, etc...).<br><br>HTH
If the problem is you cant open the connection to a password protected database, make sure you add the database password argument to the connection string.<br><br>DIM ADODBConnection as ADODB.Connection<br>DIM rs as ADODB.Recordset<br>DIM sSQL as String <br><br>SET ADODBConnection = New ADODB.Connection<br>Set rs = New ADODB.Recordset <br><br>sSQL = " Select * FROM Tablename"<br> ADODBConnection.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;" & _"DATA SOURCE=" & App.Path & "\databasename.mdb" & ";" & _<br>"Jet OLEDB
atabase Password=" & "yourDatabasePassword"<br><br><br>rs.Open sSQL,ADODBConnection,adOpenKeyset,AdLockREadOnly,AdCmdText<br><br><br>with rs<br>' Do Whatever<br>end with<br><br>Set rs = nothing<br>Set ADODBConnection = nothing<br><br>Hope this helps<br>Stay Lucky<br>Will<br><br>
atabase Password=" & "yourDatabasePassword"<br><br><br>rs.Open sSQL,ADODBConnection,adOpenKeyset,AdLockREadOnly,AdCmdText<br><br><br>with rs<br>' Do Whatever<br>end with<br><br>Set rs = nothing<br>Set ADODBConnection = nothing<br><br>Hope this helps<br>Stay Lucky<br>Will<br><br>- Status
Similar threads
