HELP Error: Trying to populate userform fields from access databas
 

Hi All, I have been struggling with this since long, I would appreciate if
anyone can help me with getting this done. Basically I want to pull
information from access database and populate my excel userform fields.
For eg: If I input a Student Id field I want to populate the Name and Phone
number for that student from access database, Here is the code that I have so
far, But it gives me errors.. Please guide me through this


Private Sub StudentId_AfterUpdate()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Name, Phone FROM Student_Info;"
Set cnt = New ADODB.Connection
With cnt
..Provider = "Microsoft.Jet.OLEDB.4.0"
..Properties("Data Source") = "C:\My Documents\Students.accdb"
..Properties("Jet OLEDB:Database Password") = "mystudents"
..Open
End With

Set rst = New ADODB.Recordset
With rst
..CursorType = adOpenKeyset
..LockType = adLockOptimistic
..Open strSQL, cnt
End With

With UserForm1
Me.Name.Value = rst.Fields(7)
Me.Phone.Value = rst.Fields(9)
End With

rst.Close
cnt.Close

End Sub

Thanks in advance

HELP Error: Trying to populate userform fields from access databas
 

Hey Sam --

I think you are using the wrong connection string - change
..Provider = "Microsoft.Jet.OLEDB.4.0"

to

..Provider = "Microsoft.ACE.OLEDB.12.0"

since you are using Access 2007 file vs Access 2003 (2003 uses Jet)

Jim

"sam" wrote:

Hi All, I have been struggling with this since long, I would appreciate if
anyone can help me with getting this done. Basically I want to pull
information from access database and populate my excel userform fields.
For eg: If I input a Student Id field I want to populate the Name and Phone
number for that student from access database, Here is the code that I have so
far, But it gives me errors.. Please guide me through this


Private Sub StudentId_AfterUpdate()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Name, Phone FROM Student_Info;"
Set cnt = New ADODB.Connection
With cnt
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "C:\My Documents\Students.accdb"
.Properties("Jet OLEDB:Database Password") = "mystudents"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSQL, cnt
End With

With UserForm1
Me.Name.Value = rst.Fields(7)
Me.Phone.Value = rst.Fields(9)
End With

rst.Close
cnt.Close

End Sub

Thanks in advance