Recordset/SQL problem - not returning correct record or returns an err

[af390]

I have the following simple code to create a recordset (rst) that I then need to re-query based on entries from a user.

Set rst = db.OpenRecordset(SELECT * FROM tablename)

when this line executes I get 1 record.

When I execute Set rst = db.OpenRecordset(SELECT DISTINCT * FROM tablename) I get runtime error 3163 the field is too small to accept the data you tried to add. Try inserting or pasting less data.

this table only has 79 records in it......


Any suggestions?

 

[LittleSmudge]

Seems simple enough.

Try this

Set rst = db.OpenRecordset(SELECT PrimeKey FROM tablename)


Where PrimeKey is the name of the primary key in the table.


Then lets see what record count that brings back.

It it brings all records then try the Distinct on it an see what happens then.


Keep adding or varying fields to see if you can locate any problem.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[cmmrfrds]

Shouldn't this be a string literal?

Set rst = db.OpenRecordset(SELECT * FROM tablename)

Set rst = db.OpenRecordset("SELECT * FROM tablename")

 

[LittleSmudge]

Well spotted cmmffrds.

It's been so long since I delved into A97 DAO code !





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[af390]

Thank you. I do have it as a string literal, in my haste I left out the quotes in my post. Silly me. I would love to use ADO, but am stuck trying to fix clients old database.

 

[LittleSmudge]

Oh - I know af390 - I've got the same challenge looming on my horizon. A client's A97 db needs an upgrade. They are only using A97 because THIER client has a database that this interfaces with in A97 and the person on the end of the chain refuses to update. ( some comment about "If it ain't broke don't f.. it ").


ho hum.

Back to your original problem - any joy with ( feedback from ) my original suggestions ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[adonet]

What is backend? If it is SQL server, need something like:
Set rec = dbs.OpenRecordset("selct...", dbOpenSnapshot, dbSeeChanges)

 

[af390]

Thanks for all the valuable insights. I found my answer by actually paying attention to the arguments for the OpenReport method. All I have to do is pass a WHERE clause and Access applies it to my underlying query. Works like a charm.

Thanks again all!