Connecting VB to Access with ADODC -- need a bit of help....

Connecting VB to Access with ADODC -- need a bit of help....

Post by wri.. » Sat, 06 Oct 2001 14:31:04



I'm taking an advanced VB course, and we are (trying) to learn to
connect a database to the application.  However, even the instructor
is fumbling on some points, and things that should work according to
my two textbooks don't seem to.  So, here's the situation.  I have a
database for a hypothetical apartment complex, with two tables -- one
for renters, and one listing apartments.

1.  I can set up the database connection string just fine as a design
time property of the ADODC control.  However, it then seems "locked"
into the connection with the table specified in the ADODC Properties
dialog, on the RecordSource tab, in the Table Or Stored Procedure Name
field.  

In other words, suppose on the ADODC Properties dialog I specify the
"RenterInfo" table as the RecordSource.  If, in code, I then use the
line
        adoMaster.RecordSource = "Apartments"
to change the record source, the program does not seem to recognize
this.  As a result, I cannot access any of the fields in the
Apartments table.  

(So, I CAN say    frmMaster.txtFirstName.Text =
frmMaster.adoMaster.Recordset("FirstName")

but I cannot say  
 frmMaster.cboApt.Text = frmMaster.adoMaster.Recordset("Apt"),

since I can't seem to set the record source to the Apartments table.)

2.  I thought maybe it would be better to delete the connection
information from the ADODC properties dialog, and specify it entirely
in code.  This would also address the problem of hardcoding the
connection information to a specific location on my hard drive.  The
code, based on the text, is:

 adoMaster.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & App.Path & "\LowHorizon.mdb"
    adoMaster.CommandType = adCmdTable
    adoMaster.RecordSource = "RenterInfo"

However, the program doesn't seem to acknowledge this at all.  When I
use a command such as:

    adoMaster.Recordset.MoveFirst

(or any other command with the RecordSet) I get an error:  "Object
variable not set"

3.  Also, if I want to bind a control to the database, at run time
rather than design time, I am having troubles with this too.

When I try to run the code:
    txtFirst.DataSource = adoMaster
    txtFirst.DataField = "First"

".DataSource" gets highlighted, and I get the message "Method or data
member not found.  Yet DataSource is clearly a data member of a text
box!

4.  Is there anyplace I can download some documentation on ADO and VB
in html, MS Word, or pdf format?

Help!   And thanks.

Steve O.

 
 
 

Connecting VB to Access with ADODC -- need a bit of help....

Post by Steven J.M » Sat, 06 Oct 2001 23:52:45


1) + 2)  The easiest way to change the recordsource (at
least how I do it (If I use ADODC!!)) is not to use
Commandtype: 2 - adCmdTable but use the 8 - adCmdUnknown
instead.
The give the recordsource not the name of the table but a
SQL-statement.
Like: SELECT * FROM [tableName]
To switch recordsource just switch SQL statement and
refresh the ADODC control.

3)  txtFirst.DataSource is an object and you need to set
an object, like this : Set txtFirst.Datasource = adoMaster

Hope this is helpfull....

Quote:>-----Original Message-----
>I'm taking an advanced VB course, and we are (trying) to
learn to
>connect a database to the application.  However, even the
instructor
>is fumbling on some points, and things that should work
according to
>my two textbooks don't seem to.  So, here's the

situation.  I have a
Quote:>database for a hypothetical apartment complex, with two
tables -- one
>for renters, and one listing apartments.

>1.  I can set up the database connection string just fine
as a design
>time property of the ADODC control.  However, it then
seems "locked"
>into the connection with the table specified in the ADODC
Properties
>dialog, on the RecordSource tab, in the Table Or Stored
Procedure Name
>field.  

>In other words, suppose on the ADODC Properties dialog I
specify the
>"RenterInfo" table as the RecordSource.  If, in code, I
then use the
>line
>    adoMaster.RecordSource = "Apartments"
>to change the record source, the program does not seem to
recognize
>this.  As a result, I cannot access any of the fields in
the
>Apartments table.  

>(So, I CAN say    frmMaster.txtFirstName.Text =
>frmMaster.adoMaster.Recordset("FirstName")

>but I cannot say  
> frmMaster.cboApt.Text = frmMaster.adoMaster.Recordset
("Apt"),

>since I can't seem to set the record source to the
Apartments table.)

>2.  I thought maybe it would be better to delete the
connection
>information from the ADODC properties dialog, and specify
it entirely
>in code.  This would also address the problem of
hardcoding the
>connection information to a specific location on my hard
drive.  The
>code, based on the text, is:

> adoMaster.ConnectionString

= "Provider=Microsoft.Jet.OLEDB.4.0;" & _

- Show quoted text -

Quote:>    "Persist Security Info=False;" & _
>    "Data Source=" & App.Path & "\LowHorizon.mdb"
>    adoMaster.CommandType = adCmdTable
>    adoMaster.RecordSource = "RenterInfo"

>However, the program doesn't seem to acknowledge this at
all.  When I
>use a command such as:

>    adoMaster.Recordset.MoveFirst

>(or any other command with the RecordSet) I get an
error:  "Object
>variable not set"

>3.  Also, if I want to bind a control to the database, at
run time
>rather than design time, I am having troubles with this
too.

>When I try to run the code:
>    txtFirst.DataSource = adoMaster
>    txtFirst.DataField = "First"

>".DataSource" gets highlighted, and I get the

message "Method or data

- Show quoted text -

Quote:>member not found.  Yet DataSource is clearly a data
member of a text
>box!

>4.  Is there anyplace I can download some documentation
on ADO and VB
>in html, MS Word, or pdf format?

>Help!   And thanks.

>Steve O.

>.


 
 
 

Connecting VB to Access with ADODC -- need a bit of help....

Post by Microsoft Developer Suppo » Sun, 07 Oct 2001 04:35:24


HI,
        To answer your question on Documentation, the best place to look is
support.microsoft.com and search on ADO, you'll get tons of hits. As for
you ADODC problem, once you make changes to the connectstring or
recordsource you have to call ADODC.Refresh for them to take affect. That
should do it for you and you can leave the ADODC setting at design time if
you like.

Thanks,
Terrell A.
Microsoft Developer Support