topic Re: Database Accsess in VBA Forum

Database Accsess

Anonymous — Sat, 19 Aug 2006 05:15:26 GMT

Hi all

Here is my code to extract some data.(AutoCAD VBA code)

Private Sub CommandButton1_Click()
Dim rs As DAO.Recordset
Dim cn As DAO.Database

Dim a As String
a = TextBox1.Value

On Error Resume Next
Set cn = DBEngine.Workspaces(0).OpenDatabase("C:\John\X_data.mdb”)
Set rs = cn.OpenRecordset("SELECT code FROM table1 WHERE Char ='" + CStr(a) + "'")

If Err Then MsgBox Err.Description
TextBox2.Value = rs.Fields(0).Value

Set cn = Nothing
Set rs = Nothing

End Sub

My above code is working fine, but if I lock the database (Locking database with user name “Admin” and password ”xyz”, I don’t know how to unlock through the code and read values.

Please help me

Thanks
Benny

Re: Database Accsess

Anonymous — Sat, 19 Aug 2006 11:28:58 GMT

Benny,

If your database is locked with an admin password you are able to access it via DAO using either the OpenDatabase or OpenConnection methods.

Since your using the OpenDatabase method, the DAO help files indicate the method arguments are:

OpenDatabase (dbname, options, read-only, connect)

With your code your only using the dbname argument.

Options will allow you to open the database in Exclusive (True) or Shared mode (False) which is the default.

Read-Only will allow you to open the database in a Read Only mode (True) or the default which is Read/Write access (False).

Connect specifies your connection info and password.

So to get what you want try changing:

Set cn = DBEngine.Workspaces(0).OpenDatabase("C:\John\X_data.mdb”)

To

Set cn = DBEngine.Workspaces(0).OpenDatabase("C:\John\X_data.mdb”, False, False, "pwd=YourPassword")

When someone posts a connect to MS Access using DAO it is inevitable that someone comes along and shouts you should be using ADO....which I would agree.

To get you trying that path read up here on opening an Access MDB that is password protected with ADO:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q191/7/54.ASP&NoWebContent=1

G'Luck,

Bob Coward

Re: Database Accsess

Anonymous — Sun, 20 Aug 2006 13:12:04 GMT

DAO is pretty much out of date, and ActiveX Data Objects (ADO) is much
easier and can do much more. Add a reference to the ADO library and try:

Dim a As String
a = TextBox1.Value
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim strSQL As String
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
oConn.Open "C:\John\X_data.mdb”, "Admin", "xyz"
strSQL = "SELECT code FROM table1 WHERE Char ='" + CStr(a) + "'"
oRS.Open strSQL, oConn
oConn.Close
Set oConn = Nothing
Set oRS = Nothing

Note; I haven't tested this, you may have to replace the single-quotes in
the SQL string with double-quotes, or change the Jet OLEDB:ODBC Parsing
property (in the Properties collection of the Connection object) to True.

In addition to any replies you might receive or already received, you may
find more information or responses by posting future connectivity related
questions in the following discussion group:

Web browser: <>
Newsreader: <>

--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services

On Sat, 19 Aug 2006 05:15:26 +0000, benny wrote:

> Hi all
>
> Here is my code to extract some data.(AutoCAD VBA code)
>
> Private Sub CommandButton1_Click()
> Dim rs As DAO.Recordset
> Dim cn As DAO.Database
>
> Dim a As String
> a = TextBox1.Value
>
> On Error Resume Next
> Set cn = DBEngine.Workspaces(0).OpenDatabase("C:\John\X_data.mdb”)
> Set rs = cn.OpenRecordset("SELECT code FROM table1 WHERE Char ='" +
CStr(a) + "'")
>
> If Err Then MsgBox Err.Description
> TextBox2.Value = rs.Fields(0).Value
>
> Set cn = Nothing
> Set rs = Nothing
>
> End Sub
>
> My above code is working fine, but if I lock the database (Locking
database with user name “Admin” and password ”xyz”, I don’t know how to
unlock through the code and read values.
>
> Please help me
>
> Thanks
> Benny

Re: Database Accsess

Anonymous — Mon, 21 Aug 2006 03:56:03 GMT

Thanks bcoward for your detailed explanation.

Modified my code i e
set cn=DBEngine.Workspaces(0).OpenDatabase("C:\John\X_data",False,False,"pwd=xyz")

But it is giving error(no-3151)- ODBC connection to "C:\John\X_data.mdb" failed.

Can you help in to solve this problem.

You have mention me to use ADO but I dont know which are the refrences to be added for ADO,i tried adding some
but it is giving me error declaration stage itself.

Re: Database Accsess

Anonymous — Mon, 21 Aug 2006 03:58:05 GMT

thanks jon

But can you explain me which are the refrences to be added for ADO.

Re: Database Accsess

Anonymous — Mon, 21 Aug 2006 09:37:49 GMT

Thanka all
with little bit trial and error I GOT IT !!!!

Re: Database Accsess

Anonymous — Mon, 21 Aug 2006 12:43:13 GMT

On Mon, 21 Aug 2006 03:58:05 +0000, benny wrote:

> But can you explain me which are the refrences to be added for ADO.

In case you still need this: in The Visual Basic editor (Alt-F11) click
"Tools" "References", scroll down to "Microsoft ActiveX Data Objects ...
Library", and put check-mark next to the highest numbered version listed.

--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services

Re: Database Accsess

Anonymous — Mon, 05 Oct 2015 12:44:38 GMT

I need to find out how to access the code for 123d design. any idea on how I do that?