Board index Database Programming

SQL AND RECORDSETS

SQL AND RECORDSETS

Post by Vipul Desa » Thu, 23 Oct 1997 04:00:00



I am having a problem of using a SQL statement in my OpenRecordset
statment. My code looks something like the following where [Product] is
an Access variable and Productstr is a global variable, which is a
string. Unfortunately, VB is returning all of the the records and not a
subset of the records as the where statement should do. Can someone
please help? I am having a tough time on this one as I just spent 3 hours  
with Microsoft technical support.

 sqlmsg = "Select All * from collapsed  _
   where collapsed.[product] = Productstr "

Set db = DBEngine.Workspaces(0).OpenDatabase("C:\PROJ1\SAMPLES.MDB")
Set rs = db.OpenRecordset(sqlmsg)

 
 
 
Top

SQL AND RECORDSETS

Post by MacKinn » Fri, 24 Oct 1997 04:00:00



>I am having a problem of using a SQL statement in my OpenRecordset
>statment. My code looks something like the following where [Product] is
>an Access variable and Productstr is a global variable, which is a
>string. Unfortunately, VB is returning all of the the records and not a
>subset of the records as the where statement should do. Can someone
>please help? I am having a tough time on this one as I just spent 3 hours  
>with Microsoft technical support.

> sqlmsg = "Select All * from collapsed  _
>   where collapsed.[product] = Productstr "

oops!  You can't have the variable in the quotes like that...try
sqlmsg="SELECT * FROM collapsed WHERE collapsed.product = " & Productstr

MacK

 
 
 
Top

1. Which is faster (ado) , SQL or recordsets?

Which method is faster? When I did this,
the recordset method was 4x faster when
inserting a single record. I thought
executing SQL queries would always be faster?

cn.connectionString =
    "Provider=Microsoft.Jet.OLEDB.4.0;...

1)
Set rsStaffUpdates = New ADODB.Recordset
CursorLocation = adUseServer
ActiveConnection = cn
CursorType = adOpenKeyset
LockType = adLockPessimistic
source = "SELECT * ...
open
AddNew
!field = ...
Update

2) cn.execute ("INSERT INTO ...")

Since Jet doesn't support dynamic cursors
and I need fresh data always, I am thinking
of using all SQL statements, and if I need
a recordset temporarily I will do this:
   set rsTemp = cn.execute(SELECT...)

By the way, everything is code, I'm not
using any cheesy controls.

Am I going about this properly?

Thanks
JMC

--Posted from EarthWeb Discussions. http://discussions.earthweb.com

2. Virtual Cube and DSO

3. Update SQL Server recordset

4. How to limit the number of records returned in a record set?

5. SQL , Disconnected recordset.

6. Cannot resize LDF file

7. SQL and recordsets

8. Sybase vs. Oracle7

9. DBGrid Error With SQL-Developed Recordset

10. Update SQL Server recordset

11. SQL Disconnect Recordset

12. ADOCE and SQL CE recordset problem

13. Problem adding new record to SQL database recordset


All times are UTC
Board index