OpenRecordset SQL statement not working

[Frank72]

Hows it going

Access seems to be reading strSQL as if it should be an object and is returning a message saying it can't find it.
Is there something wrong with my syntax here?

strSQL = "SELECT [firstName] , [secondname] , [a1] , [a2] , [customerid] From dbo_tblcustomer ORDER BY [firstname]"
' Set Outer Recordset
Set CustDb = CurrentDb
Set Rst1 = CustDb.OpenRecordset(strSQL, dbOpenTable, dbSeeChanges)


anyone?

thanks alot

Frank

 

[Mossoft]

Using dbOpenTable tells Access that your strSQL is the name of a table that it will try to open. It isn't!!
Try using dbOpenDynaset or dbOpenSnapshot depending on whether you intend to amend the data or not.

M.

 

[Frank72]

Thanks Mossoft

I already tried these 2 types but it returns err msg "Too few parameters. expected 1" 1, I presume is for OpenTable?

maybe something wrong with sql statement?

thanks

frank

 

[JoanieB]

I suppose you already tried making the data into a table with a maketable query and opening that as your recordset?

 

[jebry]

Hi Frank!

Ususally the too few parameters error indicates a misspelled field name. Mossoft is correct about the dbOpenTable, you cannot use it with an SQL string. I usually use dbOpenDynaset.

hth Jeff Bridgham
bridgham@purdue.edu

 

[Mossoft]

The SQL cannot be applied because it contains a field name that does not exist in the table. So it is assumed that the unknown field is actually a parameter and will have been supplied.

As Jebry says: Check the field names.

M.