Solved: connect with access

[Mandy]

Hi there

Can some one tell me how i can make connection between excel and Access.

What i have done so far is this:

i have added one login form in the excel sheet. I wanted to authenticate the user before he/she can use the workbook. user name and password for different users are stored in a Accessdatabase(login table having two field username and password).

here is the code that i had written so far.


Private Sub Workbook_Open()
Dim conn As Connection
Dim cmd As Command
Dim rs As Recordset
On Error Resume Next
Set conn = New ADODB.Connection
conn.ConnectionString = "provider = Microsoft.Jet.OLEDB.4.0;Date Source=C:\Users\Mandeep\Desktop\testing.mdb"
conn.Open
If Err.Number <> 0 Then
MsgBox "Connection error: " & Err.Description
Exit Sub
End If
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
Login.Show
End Sub

:banghead:
can any one advise.:dunno
Thanks
Mandeep

[Oorang]

mdmackillop has written a KB article here:
http://vbaexpress.com/kb/getarticle.php?kb_id=889

However, for this task, I think you may find using excel's native features more friendly. (2003 instructions) Go to Data>Import External Data>Import Data and just follow the prompts.

[Kenneth Hobs]

You can probably use something like this with a bit of modification. Obviously, you will want to change the database reference, the SQL string and how the data is returned. Your SQL string would get the record if the WHERE clause with your userid and password values exist.


Sub ADO()
' Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer, Row As Long, s As String

' Database information
DBFullName = ActiveWorkbook.Path & "\NWind2003.mdb"
'Exit?
If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
Cells.Clear

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Record locking
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter
'Src = "SELECT * FROM Products WHERE ProductName = 'Watch' "
'Src = Src & "and CategoryID = 30"
Src = "SELECT Orders.CustomerID, Orders.OrderDate " & _
"FROM Orders " & _
"WHERE (((Orders.OrderDate) " & _
"Between #8/1/1994# and #8/30/1994#))"
Recordset.Open Source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To .Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next Col

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

[Mandy]

Hi Oorang and Kenneth
both code are looking helpful, and i am sure i can do the rest.
One more thing how i can mark this thread as solved.

Thanks
Mandeep

[Kenneth Hobs]

In the Thread Tools menu, select the Solved option.