OpenRecordset versus SQL INSERT INTO

[dw85745]

I have several questions:

1) Why do I generate an error when attempting to add a record using VB6 OpenRecordset realtime code -- when at the same time -- I am viewing the same Table in Access (as Administrator)
2) Will adding a Record using SQL INSERT INTO instead of OpenRecordset stop this error,
and if so, why?
3) The fastest (or best) way to add a record using DAO and why
================================

OpenRecordset method:

Set rs = DaoDb.OpenRecordset(strTBLName)
With rs

.Index = "PrimaryKey"
.Seek "=", dtmHistDateTime

If .NoMatch Then
.AddNew
!fldOne = dtmTime
!fldTwo = sngData
.Update
End If

.Close
End With

==========================

SQL Param INSERT INTO method

strParam = "PARAMETERS [pOne] DATETIME, [pTwo] SINGLE; "
strSQL = strParam
strSQL = strSQL & "INSERT INTO " & strTBLName
strSQL = strSQL & " (fldOne, fldTwo)"
strSQL = strSQL & " VALUES (pOne, pTwo);"

[si_the_geek]

1) It depends on what the error is. You might be able to correct it by specifying more options for the recordset before/during opening it (such as cursor type, lock type, etc).


2) It depends on what the error is, but it might work.


3) I can't remember, because like most people I stopped using DAO many years ago - it was never meant to be used for new work in VB6, only updates to existing VB5 programs.

However, what method(s) are apt and efficient depends on the circumstances. As you seem to be performing a search before adding the record a recordset is a valid way to do it, and there is a decent chance (but not a certainty) that it is the fastest.

[dw85745]

si_the_geek:
As always thanks for responding.

====================
Regarding #1
The error I'm getting is "3262.
What I "think" is happening -- but wanting to confirm and get a solution if there is one -- is that when I view the recordset using Access itself (double_click the table name), instead of Access making a copy of the recordset for viewing, it must be using the actual recordset rather than making a copy for viewing.
Then when the VB5 App code is executed, it errors because the recordset is in use.
Seems logical, but whether correct ???

Regarding #2 (with extended question)
Again my guess is that using SQL INSERT INTO would not resolve this problem if Access is using the Actual Recordset but again want to confirm??

I would also be nice to know whether DAO and ADO methods are just wrappers around SQL, since my understanding is that Access "jet" underlying native language is in fact SQL.

Regarding #3
Most probably will need to be tested. Had hoped someone had tried it or had a comparative source.

[si_the_geek]

A quick google says this about error 3262:

Runtime error 3262 occurs if your computer has become infected with a SubSeven Trojan virus, Runtime error 3262 is resulted through a number of problems like incomplete installation or uninstalling of a program, virus attacks and confliction within the applications.

However, that is on a "free pc scan" site, so I seriously doubt that there is any truth in it whatsoever. Common sense says that you should ignore it completely.

All I do know is that it tells me more about the error than you did (and the chances are that only you can tell what the actual error message is, because database related errors usually contain specific details).


Next up, you are mis-using the word Recordset. A Recordset is the object in your code, which contains one or more rows of data that have one or more fields. While a Recordset can contain a single and entire table, neither of those are anywhere near mandatory (and both are rarely recommended). What you see in Access is either a Table, or a Query.


#1, when you open a table in Access, it does not create a copy for you, it opens the table.

Having it open will only affect your recordset(s) which use that table if you have used certain cursor types/lock types/etc (and are performing particular actions), or if you have opened the table in a "fully locked" mode in Access (such as the design view) which enforces single-user usage.

There is a chance it can be corrected (using the advice in my previous post), but it depends on the error.


#2, SQL statements do not automatically use locks or cursors (both can be hard/impossible to do even if you want to), so there is a decent chance that they will not get the same error.

DAO and ADO are basically wrappers around SQL, and so is the Access interface.

On a semi-related note, DAO is not a wise choice after about 2001 (whether you are using VB5 or VB6, or something else) due to the extreme lack of support which makes VB6 seem to be fully current, and serious chance of errors/bugs that cannot be corrected (especially on newer versions of Windows). You can use ADO in VB5/VB6 if you want, but the choice is of course yours.


#3, A comparison would be misleading/meaningless unless it was based on an extremely similar situation to yours.

While there might be people who can remember DAO well enough to give a recommendation (which is very doubtful, it is rare for anyone to even attempt to answer a DAO question), they would need more detail of the situation (such as whether the code is run in a loop, etc) to give reliable advice.

[dw85745]

Regarding error 3262 - have a description:
runtime error 3262 couldn't lock table

So don't believe virus. Since I didn't lock from code, found error description odd other than
Access must put a lock on the table when double_clicking it (the table) for viewing, which causes
the Vb5/6 OpenRecordset call to error . Access must (doublclick table) consider this "design view"
-- as you point out -- even though I did not select or enter design from the Access menu.

The above said, SQL INSERT INTO should also fail since manual viewing locks the table.

I know I should upgrade this App to ADO, but like all things time and priorities.
For that matter "Classic VB" is out of date (per M$), but I love it as it does what I want.
This goes back to the old debate of which language should you use. If M$ had their way
you would be using a new language every six months.

[si_the_geek]

Opening the table in Access the way you did is not design view, and from my experience it shouldn't lock the table to that degree (I have always been able to add/edit/delete records while a table is open that way). What it sounds like is that DAO is trying to lock the entire table, which is not good, and should be preventable by setting the relevant cursor/lock/etc options via code.

There is no valid reason to assume that SQL statements would have the same issue, so I recommend you test it.


As to ADO/DAO, it is your choice (I completely understand the issues of time etc), but in terms of support it is a completely unrelated to Classic VB, and is on a much larger scale.

While DAO and ADO are both usable from Classic VB, neither are intrinsically VB - they are external components which VB can use. Even tho there was a replacement for Classic VB about 8 years ago, it is still supported to some degree (albeit small!). DAO on the other hand has not been supported at all for many years, and is expected to give issues on modern versions of Windows - whereas ADO is fully supported, and is likely to stay that way for several years to come.

It is very likely that the database related parts of your app will completely fail due to usage of DAO many years before the app will fail due to usage of Classic VB.

[dw85745]

Opening the table in Access the way you did is not design view, and from my experience it shouldn't lock the table to that degree (I have always been able to add/edit/delete records while a table is open that way). What it sounds like is that DAO is trying to lock the entire table, which is not good, and should be preventable by setting the relevant cursor/lock/etc options via code.

Thanks for the confirmation. Will double-check my code.

There is no valid reason to assume that SQL statements would have the same issue, so I recommend you test it.

Planned to test in real-time next week. Will post results.

===========
Re: Classic VB, DAO, ADO

Point noted.