Access 2000 ADO and Subform Population

[JazzBass]

Hi gang,

I've been banging my head against my desk for around 6 hours trying to figure this out.

I'm using 2 databases. One has all data (data.mdb) and the other has the GUI stuff in it(GUI.mdb).

I'm doing it this way because we want to make the data as secured as possible.

Anyway, I would like to open a recordset in ADO and then show the results in a subform.


This is my current code.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
        

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    With cnn
        .ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=D:\DATA.mdb;"
        .Open
    End With
    'COMPANY_LIST is the table
    sSQL = "SELECT * FROM COMPANY_LIST"
    Set rs = cnn.Execute(sSQL)
        
    cnn.Close

set cnn = nothing
set rs = nothing

Can this be done?



Also,

I got it working to populate a combo box. But it comes back and says that it can only accept 2,??? characters.

What is the correct way to do this?


Please forgive my dumb questions, but database stuff is really new to me.

Help Please!!!



Thanks,
JazzBass

[ShIzO]

who comes back and tell you to use 2 charactes? is it a runtime error or AccessDB error and at which point do you get that message?

[JazzBass]

Hi,
Thanks for the reply. When I'm populating the combo box, I do a loop and add a ";" at the end of result in the field I want. If I try and let it add all the content from the that field in the table, then Access comes back and says that "can not accept a value over 2,480 characters.

I deleted the code in frustration.

The way you can populate a combo box in Access really stinks because for every value you want to put in the box, you need a ";".
Example,
"name1;name2;name3;name4;etc..."

You can't just do an additem like in standalone VB.

There is another way to populate a Combo Box by assigning it to a table or query, but I don't think I can do that trying to get records from an outside database.

Thanks for whatever help I can get.

JazzBass

[visualsander]

Hi there,

About the sub form stuff I-dun-no, I am a VB starter. Access I do know.

A tip for the combobox: instead of adding the values manualy you can use a table.

e.g. SELECT orderid FROM ORDER (or whatever)

About the subform:
In ACCESS(!) I always use a query (or SQL-statement) as the source for a sub-form.

So if you're main(vb)-form uses a SQL-statement as source why not use a different SQL-statement as source for the subform?


I studied you're code. I think that the code works. However I do not see any code where DISPLAYS the result.

Sander

PS: I was just brainstorming in my own creepy mind so dont attack me

[JazzBass]

Hi,

Thanks for the input.

In other Access programs, I have used queries as the source of a subform. The thing is that all the data was in the same database. This is my first time trying to get records from an outside database. It's kinda like if someone wanted to get data from and Oracle or Sql Server database.

Pretty much what you told me I understand and have inplemented it in the past.

The code works, but what I need is a way to display the results. I would like to do that with a subform.

I don't know how to link the subform to a ADO query. Your example is basically what I want to do, but I don't know how to get a ADO query to work with it.

Thanks again,
JazzBass