Error 3146 (VB6)

Using DAO 3.51, there is no problem.
Using DAO 3.6, the problem occurs.

I am using SQL Server, but there is some wierdness in that we are opening an
Access database "wrapper" and attaching tables to it, sometimes from two
different SQL servers. If there is a better way to be able to access tables
from multiple SQL servers in the same query other than attaching their
tables through an SQL server, that may be something to look into.

Otoh, there is no problem using DAO 3.51, so something changed when they
made 3.6. (note: 3.51 supports up to Access 97, to read Access 2000 .MDB
files you need 3.6).

The insert statement is correct. Copying the same table the failures happen
pretty much randomly -- i.e. on different records with each attempt. Also,
when I retry it (sometimes) works.

Please tell me more about setting QueryTimeout. This may be worth
investigating.

Whether the insert completes is an interesting question. The target
database has an autoincrement integer column (to provide a recordid). There
are gaps in this value where the missing records should have been, so it
appears as if the records were created (incrementing the id) but not
committed. Note, however, that we are /not/ using transaction code (begin
transaction/commit), so I don't know how this could occur.

--
-Richard M. Hartman

186,000 mi/sec: not just a good idea, it's the LAW!

You have insurance for your car and your health,
why not for your legal needs?
http://www.legalhmo.com

"Ralph" <nt_consulting64 RemoveThis @yahoo.com> wrote in message
news:OldrZ190HHA.4652@TK2MSFTNGP05.phx.gbl...
>
> "Richard M. Hartman" <hartman RemoveThis @onetouch.com> wrote in message
> news:OS9a$680HHA.2484@TK2MSFTNGP06.phx.gbl...
>> I am opening two databases, iterating through a table in one of them and
>> making "INSERT INTO" queries to copy the data to a table in the second
>> (using the execute method). The machines are both physically on the same
>> network segment.
>>
>> I am getting error 3146 (ODBC call failed).
>>
>> The help message suggests that the network might not be available and I
>> should try again. However, when this occurs at the 37th (or 85th ... or
>> 109th ...) record in the iteration loop, I fail to see how that could be
>> when it just had been fine for the previous 36 calls ...
>>
>> I have tried adding retries -- some succeed the second time around, or
> maybe
>> the third. I set a limit of 5, and some failed enough times in a row to
>> trip even that limit.
>>
>> I don't want to just up the max retry count -- sooner or later one of
>> them
>> might be stubborn enough to fail X number of times whatever value I
>> choose
>> for X.
>>
>> So ... what might be the cause of this problem? Is there perhaps some
>> network settings tuning I can do that might help? What else can I look
> at?
>>
>> --
>> -Richard M. Hartman
>>
>> 186,000 mi/sec: not just a good idea, it's the LAW!
>>
>> You have insurance for your car and your health,
>> why not for your legal needs?
>> http://www.legalhmo.com
>>
>>
>
> What database and driver are you using?
> Does the Insert complete even though it errors?
> Have you tried setting the .QueryTimeout to zero?
> Error: 3146 is a rather general error. Subsquent errors usually provide
> more
> information. What other errors are you finding in the Errors collection?
> Capture the actual Insert statement being used at the time of the error.
> It
> may not be correct?
>
> hth
> -ralph
>
>
>
 >> Stay informed about: Error 3146 (VB6) 

"Richard M. Hartman" <hartman.RemoveThis@onetouch.com> wrote in message
news:O5iGIVZ1HHA.6072@TK2MSFTNGP03.phx.gbl...
> Using DAO 3.51, there is no problem.
> Using DAO 3.6, the problem occurs.
>
> I am using SQL Server, but there is some wierdness in that we are opening
an
> Access database "wrapper" and attaching tables to it, sometimes from two
> different SQL servers. If there is a better way to be able to access
tables
> from multiple SQL servers in the same query other than attaching their
> tables through an SQL server, that may be something to look into.
>
> Otoh, there is no problem using DAO 3.51, so something changed when they
> made 3.6. (note: 3.51 supports up to Access 97, to read Access 2000 .MDB
> files you need 3.6).
>
> The insert statement is correct. Copying the same table the failures
happen
> pretty much randomly -- i.e. on different records with each attempt.
Also,
> when I retry it (sometimes) works.
>
> Please tell me more about setting QueryTimeout. This may be worth
> investigating.
>
> Whether the insert completes is an interesting question. The target
> database has an autoincrement integer column (to provide a recordid).
There
> are gaps in this value where the missing records should have been, so it
> appears as if the records were created (incrementing the id) but not
> committed. Note, however, that we are /not/ using transaction code (begin
> transaction/commit), so I don't know how this could occur.
>
> --
> -Richard M. Hartman
>

Well that's more information. But I can't help because I haven't used linked
tables that much in MSAccess. Or at least never more than one in a single
query.

As far as QueryTimeout, that is just a way of turning it off should that be
part of the problem - but of course that also runs the risk of query never
returning? <g>

I'm not too clear by what you mean by an Access wrapper. Are you working in
VB and somehow automating MSAccess, or is this "MSAccess VBA". If the latter
you might find more support in a MSAccess Developer's group or forum.

I can pass on this particular bit of advice. While DAO works well in
MSAccess and VB if using Jet, it is not optimal for working with other data
engines. You might investigate using ADO more directly through OLE DB. But
don't know how that might fit within your problem domain.

Normally when we have to use multiple SQL Servers for a query - we do it
inside SQL Server and then present a View or SP for clients from a single
server. But that would likely take a complete rework.

hth
-ralph
 >> Stay informed about: Error 3146 (VB6) 

On Aug 3, 9:15 am, "Ralph" <nt_consultin... RemoveThis @yahoo.com> wrote:
> "Richard M. Hartman" <hart... RemoveThis @onetouch.com> wrote in messagenews:O5iGIVZ1HHA.6072@TK2MSFTNGP03.phx.gbl...
>
>
>
> > Using DAO 3.51, there is no problem.
> > Using DAO 3.6, the problem occurs.
>
> > I am using SQL Server, but there is some wierdness in that we are opening
> an
> > Access database "wrapper" and attaching tables to it, sometimes from two
> > different SQL servers. If there is a better way to be able to access
> tables
> > from multiple SQL servers in the same query other than attaching their
> > tables through an SQL server, that may be something to look into.
>
> > Otoh, there is no problem using DAO 3.51, so something changed when they
> > made 3.6. (note: 3.51 supports up to Access 97, to read Access 2000 .MDB
> > files you need 3.6).
>
> > The insert statement is correct. Copying the same table the failures
> happen
> > pretty much randomly -- i.e. on different records with each attempt.
> Also,
> > when I retry it (sometimes) works.
>
> > Please tell me more about setting QueryTimeout. This may be worth
> > investigating.
>
> > Whether the insert completes is an interesting question. The target
> > database has an autoincrement integer column (to provide a recordid).
> There
> > are gaps in this value where the missing records should have been, so it
> > appears as if the records were created (incrementing the id) but not
> > committed. Note, however, that we are /not/ using transaction code (begin
> > transaction/commit), so I don't know how this could occur.
>
> > --
> > -Richard M. Hartman
>
> Well that's more information. But I can't help because I haven't used linked
> tables that much in MSAccess. Or at least never more than one in a single
> query.
>
> As far as QueryTimeout, that is just a way of turning it off should that be
> part of the problem - but of course that also runs the risk of query never
> returning? <g>
>
> I'm not too clear by what you mean by an Access wrapper. Are you working in
> VB and somehow automating MSAccess, or is this "MSAccess VBA". If the latter
> you might find more support in a MSAccess Developer's group or forum.
>
> I can pass on this particular bit of advice. While DAO works well in
> MSAccess and VB if using Jet, it is not optimal for working with other data
> engines. You might investigate using ADO more directly through OLE DB. But
> don't know how that might fit within your problem domain.
>
> Normally when we have to use multiple SQL Servers for a query - we do it
> inside SQL Server and then present a View or SP for clients from a single
> server. But that would likely take a complete rework.
>
> hth
> -ralph

We are using VB6, not VBA.

By "wrapper" I mean that we are opening an Access .MDB file
(wrapper.mdb), and attaching tables to it from different SQL servers.
This gives us an environment whereby we can transfer records from one
SQL Serer database to another with queries like "INSERT INTO
RemoteSites ( SiteID, SiteName ) SELECT DISTINCTROW
LocalSites.SiteID, LocalSites.SiteName FROM LocalSites;" where
RemoteSites and LocalSites each reside in a different SQL server but
are both attached to wrapper.mdb.

Is there another way to do a query like that to transfer data from one
SQL server to another in VB?

We may yet have to look into ADO.

What I do not understand is that this code worked fine with DAO 3.51,
but when we moved up to DAO 3.6 this started happening. Same code.
Same network. Same SQL servers. Same Access wrapper file. The /
only/ difference is the DAO version.
 >> Stay informed about: Error 3146 (VB6)