List:MySQL on Win32« Previous MessageNext Message »
From:SGreen Date:December 10 2004 3:37pm
Subject:Re: adodc issue
View as plain text  
Thank you! (BTW, it's proper to CC: the list on all responses. That way 
everyone gets to help)

The key phrase here is " you and another user are attempting to change the 
same data at the same time".  Normally we see that error phrase appear in 
the list from developers using M$ Access's linked tables (through ODBC). 
However, I suspect that your ADODC control is written similarly enough 
that it inherited this problem too.

The easiest workaround is to:
1) Make sure there is a primary key on the table you are attempting to 
update
2) Make sure there is a TIMESTAMP column on the table you are attempting 
to update. If you have to add one make sure it's a TIMESTAMP column not a 
DATETIME. Whenever I have to link Access to MySQL I call those superfluous 
columns "lastmodified".

If doing those two things doesn't work, you might try attempt to RTFM for 
some other known issues:
http://dev.mysql.com/doc/mysql/en/Programs_Known_to_Work_With_MyODBC.html
http://dev.mysql.com/doc/mysql/en/MSAccess.html

Sorry I can't be more specific but as I mentioned earlier, I never data 
bind anytime I can avoid it (just to dodge the "house of cards" you just 
ran into). Maybe others on the list can offer more helpful advice 
(anyone?).  FWIW - I even go so far as to avoid  using the ".update" 
method of an "ADODB.recordset", I always write my UPDATE statement into a 
variable and send it in using the ".execute" method of a 
"ADODB.Connection".

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Mark Mchugh <mark_mch@stripped> wrote on 12/10/2004 09:59:19 AM:

> lol, i think my teeth are almost grinded out, the data
> base i am using is mysql, vb6.0 with all the service
> packs, the i am now logging in as root, so all
> permissions should be fine. from the app, i can
> connect to the database, and read from it, and
> navigate up and down using the adodc control. the
> issue is when i change one of the fields on the form,
> and then hit the adodc contol to move up, i get the
> message "The Mircosoft jet database engine stopped the
> process becasue you and another user are attempting to
> change the same data at the same time"
> 
> this is wierd, cos when i used the folling vb vode on
> the form
> 
>     On Error Resume Next
>     For Each cnt In Me
>         If cnt.DataField <> "" Then
>         Debug.Print cnt.DataField & "   " & cnt.Name
>     Next
> 
> 
> i get a list of all the dataitems, and they are all
> unique, i dont know if i am being silly?
> 
> 
> thanks
> 
> 
> 
> --- SGreen@stripped wrote:
> 
> > Are you doing this from within Access or as a
> > stand-alone VB application? 
> > What version are you using (Access 2.0, VS.NET, ...
> > )? What are you doing 
> > when you get the error? What kind of visual control
> > are you binding your 
> > datasource to? Does the account you are using in
> > your DSN have sufficient 
> > privileges to change data in the table you are
> > connecting to?  If you turn 
> > on ODBC logging, do you see any errors in the trace?
> >  I know you can test 
> > the ability to *connect* from the Control Panel but
> > have you actually 
> > retrieved or updated any data through that DSN by
> > some method?
> > 
> > Have you heard the old saying "The Devil is in the
> > details" ?  That goes 
> > double for OLE (ActiveX) automation controls from
> > M$. If your ducks aren't 
> > in a row and marching in step, their controls can do
> > some crazy things. 
> > Have you searched http://msdn.microsoft.com  for
> > other examples of using 
> > an ODBC DSN with datasource controls? 
> > 
> > Let me know what you find out. Don't make me come
> > over there and start 
> > pulling teeth ;-)
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > Mark Mchugh <mark_mch@stripped> wrote on 12/10/2004
> > 09:29:51 AM:
> > 
> > > Hi Shawn,
> > > here's how i set-up the control, 
> > > 
> > > adodc1.Connect = "odbc;DSN=myodbc3-test"
> > > 
> > > the i use adodc1.recordsource = "Select * from
> > table;"
> > > 
> > > i use the standard Microsoft datasource etc for
> > each
> > > item on the form, i.e. the datasource is ADODC1,
> > and
> > > the datafield is the name in the table i want. The
> > DSN
> > > i am using works fine when i rest from the control
> > > panel, any ideas? it seems very wierd to me?
> > > 
> > > many thanks for your time
> > > 
> > > MArk
> > > 
> > > --- SGreen@stripped wrote:
> > > 
> > > > OK, I can tell by your code that you are using a
> > > > Basic variant (Visual 
> > > > Basic or VBA) but I don't see a reference to
> > your
> > > > control (are you using a 
> > > > control? Which one?)  I see nothing wrong with
> > what
> > > > you posted so far. 
> > > > Also your example shows you using an
> > > > ADODB.Connection not an ADODC 
> > > > control. Which is it? Are you using a data bound
> > > > control or are you using 
> > > > the ADODB objects directly? 
> > > > 
> > > > Please answer my other questions (from my first
> > > > response), too,  as I am 
> > > > still under-informed of what you are attempting
> > to
> > > > do. I have loads of 
> > > > experience using the ADODB library directly, I
> > just
> > > > need more details 
> > > > about what you are wanting to do, how you are
> > doing
> > > > it, and what you hope 
> > > > to achieve.
> > > > 
> > > > Thanks!
> > > > 
> > > > Shawn Green
> > > > Database Administrator
> > > > Unimin Corporation - Spruce Pine
> > > > 
> > > > 
> > > > Daniel da Veiga <danieldaveiga@stripped> wrote
> > on
> > > > 12/10/2004 08:34:16 AM:
> > > > 
> > > > > I used ADODB objects with VBA to connect to
> > MySQL
> > > > and it worked, did
> > > > > you include the right references for your
> > project?
> > > > You may try
> > > > > installing the most recent MDAC for Windows.
> > You
> > > > may have a problem
> > > > > with ADO, by the time I worked with this I was
> > > > using 2.7 recordset
> > > > > library.
> > > > > 
> > > > > Thats it, hope that helps,
> > > > > 
> > > > > (by the way, someone really hates me on this
> > list,
> > > > I got a unsubscribe
> > > > > request from  217.91.105.119) :P *LOL*
> > > > > 
> > > > > On Fri, 10 Dec 2004 03:41:39 -0800 (PST), Mark
> > > > Mchugh
> > > > > <mark_mch@stripped> wrote:
> > > > > > hi,
> > > > > > my connection string is
> > > > > > 
> > > > > > Set connMySQL = New ADODB.Connection
> > > > > >     connMySQL.ConnectionString =
> > "DRIVER={mySQL
> > > > ODBC
> > > > > > 3.51 Driver};" _
> > > > > >         & "SERVER=192.168.0.55;" _
> > > > > >         & "DATABASE=adjust;" _
> > > > > >         & "UID=*****;" _
> > > > > >         & "PWD=*****;" _
> > > > > >         & "OPTION=" & 1 + 2 + 8 + 32 + 2048
> > +
> > > > 16384
> > > > > > 
> > > > > > i'm completely lost!!!!
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > --- SGreen@stripped wrote:
> > > > > > 
> > > > > > > You could stop confusing apples with
> > oranges
> > > > to
> > > > > > > start with.  I can tell
> > > > > > > from your post that you are developing
> > > > something
> > > > > > > form-based (but I have no
> > > > > > > clue in which language) and are attempting
> > to
> > > > use a
> > > > > > > data-bound ADO-based
> > > > > > > control. Unfortunately, I am one of those
> > > > > > > loggerheads who prefer to do
> > > > > > > everything by hand (no binding, ever) so I
> > > > have no
> > > > > > > experience with that
> > > > > > > particular OCX. What I can tell you is
> > that if
> > > > your
> > > > > > > control thinks that
> > > > > > > your MySQL server is a Jet-type database,
> > you
> > > > have
> > > > > > > something seriously
> > > > > > > messed up.
> > > > > > >
> > > > > > > What does your connection string look
> > like?
> > > > > > > (obfuscate your login,
> > > > > > > servername, and password, of course).  How
> > are
> > > > you
> > > > > > > trying to bind that
> > > > > > > control to something in the database? Are
> > you
> > > > going
> > > > > > > through an ODBC driver
> > > > > > > or Connector/Net or trying a direct
> > connection
> > > > from
> > > > > > > the control?  Please
> > > > > > > provide whatever details you can.
> > > > > > >
> > > > > > > Shawn Green
> > > > > > > Database Administrator
> > > > > > > Unimin Corporation - Spruce Pine
> > > > > > >
> > > > > > > Mark Mchugh <mark_mch@stripped> wrote on
> > > > 12/09/2004
> > > > > > > 12:06:14 PM:
> > > > > > >
> > > > > > > > hi,
> > > > > > > > I am using databinding with the above
> > named
> > > > > > > control,
> > > > > > > > but every time i make a change , i get
> > the
> > 
> === message truncated ===
> 
> 
> 
> 
> __________________________________ 
> Do you Yahoo!? 
> Jazz up your holiday email with celebrity designs. Learn more. 
> http://celebrity.mail.yahoo.com

Thread
adodc issueMark Mchugh9 Dec
  • RE: adodc issueJ.R. Bullington9 Dec
  • Re: adodc issueSGreen9 Dec
    • Re: adodc issueMark Mchugh10 Dec
      • Re: adodc issueDaniel da Veiga10 Dec
        • Re: adodc issueSGreen10 Dec
    • Re: adodc issueGraeme St.Clair11 Dec
      • Re: adodc issueRandy Clamons13 Dec
Re: adodc issueSGreen10 Dec