Hi Joel,

Here is what I have so far... What I want it to do is.. On excel userform..
when I input the studentId, I want certain other fields like, Student name,
Subjects to auto populate,, and all this details are in access database. Hope
you can help me with this.

Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC just
'indicating where the file is located

Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

rs.Open "Loan_Presentation", cn, 1, 3, 2

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:Documents\Students.accdb;DefaultDir =C:\Documents\" _
), Array( _
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;;UID=admin ;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT Students.Roll_No, Students.Name" & "FROM
`C:\DOCUMENTS\Students.accdb`.Students Students" _
& "WHERE Students.Roll_No = StudentId.value")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With
End Sub

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


"joel" wrote:


Opening a database you don't need to open a file like opening an excel
file. You need to do 3 tihings


1) Open a connection to provide a path from the excel application to
the database. The connection could be over an internet or on a PC just
indicating where the file is located

2) Open a recordset which is a set of instructions of what data you
want returned. This includes a SQL (Script Query Language).

3) Move the Recordset data to the workbook. All the items in the
recordset contains the data you required becasue you provided the
filtering in the SQL.


What I usually recommmend to get the SQL corrrect is to perform a
manual query while recording a macro. The Command Text portion of the
query contains the SQL.


First start a macro recorder.

Tools - Options - Start Recording

Second perform a manual query

Data - Import External Data - New Database Query

Select the type of database and located the file on your PC

Using the Wizard
menu 1 - Select the table a fields you want returned
menu 2 - Select the filtering you want applied like the ID
menu 3 - select any sorting you want
menu 4 - Press Finish. You can examine the SQL by selecting the Edit
query button and pressing Finish. The locate the SQL button in the
Query Editor.

When done post the record macro and I will make the necessary changes.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

.