Visual Basic&database, HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Anne G. Leg
#1 / 5

HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Quote:

>Hi! I've coded a number of VB3.0 / ACCESS 2.0 apps with no problem. The
>current app I'm working on is giving me some grief though. I;m running
>the 2.5 Jet Engine with a small database. I'm using multiple inner
>joins. Somehow during development runs the access to the database gets
>switched to READONLY. I have no code to my knowledge that is doing this.
> I'm not using any data controls...everything is done with code. I open
>the database with the statement SET DB = OPENDATABASE(DBX,0,0) where DBX
>is a database name passed on the command line. If anyone has experienced
>a similar problem please reply.

> Thanks in advance.
> Steve

Question: You use multilpe inner joins, so is it possible that you have
a one to many relationship going here that will disallow updates? This
really applies to Dynasets, but sometimes we forget the difference.

When created from joins, a Dynaset has to follow these rules to be updatable:

1.) The Select statement must include DISTINCTROW. This causes the set
returned to include each row in which any of the values of all fields
in each table differs.
2.) If joined on a many-to-one (one-to-many) relationship, Select
Distinctrow statement can't include any field from the table that
represents the "one" side of the relationship.
3.) The table that represents the "one" side of the many-to-one (or vice
versa) relationship must have a primary key or unique index.
4.) The query cannot contain any self-join, include totals, or be a
crosstab query.

So, if you did one of those, that's probably the problem. The dynaset
created will automatically not be updatable. If you were to save this
dynaset into a table, the table might inherit that also (don't really know).
I had just passed a discussion of this in one of my books, and it didn't
mention what would happen in this case.

Hope this helps,
Michael

Sun, 17 Aug 1997 03:30:48 GMT

Steve Sosk
#2 / 5

HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Hi! I've coded a number of VB3.0 / ACCESS 2.0 apps with no problem. The
current app I'm working on is giving me some grief though. I;m running
the 2.5 Jet Engine with a small database. I'm using multiple inner
joins. Somehow during development runs the access to the database gets
switched to READONLY. I have no code to my knowledge that is doing this.
I'm not using any data controls...everything is done with code. I open
the database with the statement SET DB = OPENDATABASE(DBX,0,0) where DBX
is a database name passed on the command line. If anyone has experienced
a similar problem please reply.

Thanks in advance.
Steve

Sat, 16 Aug 1997 11:42:20 GMT

Eric Steim
#3 / 5

HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Quote:

>Hi! I've coded a number of VB3.0 / ACCESS 2.0 apps with no problem. The

{stuff deleted}

is a database name passed on the command line. If anyone has experienced

Quote:

>a similar problem please reply.

> Thanks in advance.
> Steve

We have had a similiar problem, but this was with a data control. We had a control
on the login form that opened that database readonly. After the form was unloaded, all
of the following opens were forced readonly regardless of the parms. (this probably doesn't
help you though... sorry...

Eric

Sat, 16 Aug 1997 23:13:47 GMT

Jamie Rubi
#4 / 5

HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Quote:

> Hi! I've coded a number of VB3.0 / ACCESS 2.0 apps with no problem. The
> current app I'm working on is giving me some grief though. I;m running
> the 2.5 Jet Engine with a small database. I'm using multiple inner
> joins. Somehow during development runs the access to the database gets
> switched to READONLY. I have no code to my knowledge that is doing this.
> I'm not using any data controls...everything is done with code. I open
> the database with the statement SET DB = OPENDATABASE(DBX,0,0) where DBX
> is a database name passed on the command line. If anyone has experienced
> a similar problem please reply.

> Thanks in advance.
> Steve

This is what I tried, and what seemed to work: when you create your
*dynaset* (set ds = db.createdynaset(SQL), vb/access defaults to all
updates that only affect one "row" in your dynaset. The default code
could also look like:

set ds = db.createdynaset(SQL,32)

where the 32 is a value for the vb's default. In the documentation
there is a constant (value = 16) that allows writes to multiple "rows"
in your dynaset. The code is:

set ds = db.createdynaset(SQL,16)

wherever you have mulitple joins. Try this. It worked for me and I'm
coding for a multiuser enviorment as well...

-Jamie Rubin

Sun, 17 Aug 1997 01:18:09 GMT

Steve Sosk
#5 / 5

HELP - VB PRO /ACCESS 2.0 READ ACCESS ONLY mystery

Lege ) writes:

Quote:

(Steve Soskin) writes:
>>Hi! I've coded a number of VB3.0 / ACCESS 2.0 apps with no problem.

The
snip

Quote:

>Question: You use multilpe inner joins, so is it possible that you
have
>a one to many relationship going here that will disallow updates? This
>really applies to Dynasets, but sometimes we forget the difference.

>When created from joins, a Dynaset has to follow these rules to be
updatable:

>1.) The Select statement must include DISTINCTROW. This causes the set
> returned to include each row in which any of the values of all
fields
> in each table differs.
>2.) If joined on a many-to-one (one-to-many) relationship, Select
> Distinctrow statement can't include any field from the table
that
> represents the "one" side of the relationship.
>3.) The table that represents the "one" side of the many-to-one (or
vice
> versa) relationship must have a primary key or unique index.
>4.) The query cannot contain any self-join, include totals, or be a
> crosstab query.

>So, if you did one of those, that's probably the problem. The dynaset
>created will automatically not be updatable. If you were to save this
>dynaset into a table, the table might inherit that also (don't really
know).
>I had just passed a discussion of this in one of my books, and it
didn't
>mention what would happen in this case.

>Hope this helps,
>Michael

Michael:

added a ",16" to the dynaset and that dis the trick. It looks like it's
working fine now. There was one other problem, an intermittent GPF that
looks like it was caused by 3 arrays of masked edit control boxes on 3
seperate panels on 1 form, but that's another story.

Thanks again,
Steve

Sun, 17 Aug 1997 09:05:20 GMT