datagrid.Datasource error

[klasse]

Hi,
I have an application in VB 6. Users log to a Ms Access database, then I keep in a hidden form the name of the user.
Also I have the following code in a form where I the only object I have is a datagrid (datagrid1) and nothing else.

*****************************************
Private Sub Form_Load()
Dim ADOconnection As ADODB.Connection
Dim ADOrst As ADODB.Recordset
Dim connectString As String

Set ADOconnection = New ADODB.Connection
Set ADOrst = New ADODB.Recordset
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data source=c:\mydatabase.mdb"

ADOconnection.Open connectString
ADOrst.Open "SELECT MYNAME, MYSURNAME, MYJOB, MYDEPT, MYOFFICE, MYTF FROM TABLE1, TABLE2 WHERE MYNAME = '" & frmtrack.txtmydept & "'", ADOconnection

Now when I do ...
DataGrid1.DataSource = ADOrst
... and run the application, it gives me the compile error "Method or data not found"
and it refers to Datasource in such line.

*******************************************

What is the problem?

Thanks in advance!

Klasse

 

[Sawatzky]

dear Klasse,

try

set DataGrid1.DataSource = ADOrst

I am not sure if it helps in this case but often it does.

HTH

regards Astrid

 

[klasse]

Hi,

I tried that, but I get the error 7004: The rowset is not bookmarkable.

Thanks for your interest,

Klasse

 

[Sawatzky]

dear klasse,

you are 1 step further now, as the datasource is set but not accepted,

in your select statement you have not defined how to join the tables

"SELECT MYNAME, MYSURNAME, MYJOB, MYDEPT, MYOFFICE, MYTF FROM
TABLE1, TABLE2 WHERE MYNAME = '" & frmtrack.txtmydept & "'"

to get the right select statement,
1)build a query in your sourcedatabase .
2) switch to the sql-view of the query
3) copy and paste into your code


HTH

Astrid

 

[klasse]

Sorry, my mistake.
In the original I have the link, but I forgot to add a similar thing in this one.

I would have
ADOrst.Open "SELECT MYID, MYNAME, MYSURNAME, MYJOB, MYDEPT, MYOFFICE, MYTF FROM TABLE1, TABLE2 WHERE TABLE1.MYID = TABLE2.MYID AND MYNAME = '" & frmtrack.txtmydept & "'", ADOconnection

I actually had used the SQLview on Access for it!

Thanks and... any more ideas?,

Klasse

 

[Sawatzky]

Dear klasse,

try to change the options of the open statement, to acdynamic .

regards Astrid

 

[klasse]

Hi Astrid,

Where do I have to change to acdynamic (or adOpenDynamic)?

Do you mean like this?

ADOrst.Open "SELECT MYID, MYNAME, MYSURNAME, MYJOB, MYDEPT, MYOFFICE, MYTF FROM TABLE1, TABLE2 WHERE TABLE1.MYID = TABLE2.MYID AND  MYNAME = '" & frmtrack.txtmydept & "'", ADOconnection, adOpenDynamic

I tried it now and did not change anything.
I tried also acdynamic (just in case) but nothing changed either.

Klasse