This is a good question.



Your problem is not with your SQL, although there may be a problem there
also.



The problem is in this line of code:

rsData.Open sSQL, sConnect, 3, 1

You must use gcnAccess, not sConnect, like this:

rsData.Open sSQL, gcnAccess, 3, 1



You can also make sure after using gcnAccess.Open that it worked ok, that
is, that the connection  is now open, in the debugger while stepping through,
that gcnAccess.State = 1 (adStateOpen). If it is not, then the connection is
not open.



In your SQL, make sure you have a space in the string between words,
sometimes it can get confusing. For example, in your code:

sSQL = "SELECT [Report Category],[PQR Number],[Create-date]" & _

"FROM [Closed Remedy PQR] WHERE [Closed Remedy PQR].[Create-Date] > Date()-8
And [Closed

Remedy PQR].[Report Category] <> " & StrSearchtxt & ";"



There is no space before the word FROM, so the SQL looks like:

"SELECT ...[Create-date]FROM [Closed..." and you can see the FROM needs a
space before it.



But your main problem is the rsData.Open statement.

A few points, some of which might help ...

1) Please tidy up your code. In particular, indent it properly, so it's
readable.

2) Your connection  string is quite different to one I've used to open an
Access database from Excel. I don't know what the significance of this is,
but FYI I'm including mine.

Private mConnection As New ADODB.Connection
Call mConnection.Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
Path & "\Production substitute.mdb")

I pass the connection string as a parameter on the Open call, but I doubt
that this is significant.

3) I'm unhappy about your second sSQL string. Two things. Firstly, there
is no space between [Create-date] and FROM. This might be confusing Access.
Secondly, you aren't generating quotes in the SQL around StrSearchtxt
although it presumably contains a string.

You absolutely need to put a display/msgbox of sSQL before trying to use
it, so that you can check what the SQL statement  really looks like.

I suggest you stick with your first SQL statement for the moment - it's much
simpler and eliminates one area of uncertainty.

4) You have three rsData.Open statements:

'rsData.Open sSQL, sConnect
'rsData.Open sSQL, sConnect, 3, 1
rsData.Open

The last of these is the one that's not commented out - but it gives no
information on what database to open or what SQL to execute. That's never
going to work.

The other two are similar. They pass the SQL to the Open, and the
connection STRING. I doubt that this is right. You need to pass the
connection itself.

An open from my code:

Dim RS As ADODB.Recordset: set  RS = New ADODB.Recordset
Call RS.Open(Source:=SQL, ActiveConnection:=mConnection,
CursorType:=adOpenKeyset)

The first line could presumably also be a direct New call:

Dim RS As New ADODB.Recordset
Call RS.Open(Source:=SQL, ActiveConnection:=mConnection,
CursorType:=adOpenKeyset)

My suspicion is that (4) is the most important of these points. You need to
make sure that the RS Open has the information needed to attach it to the
connection.

The error  means it is not able to open a connection. I dont even think it is
hitting the SQL statements.
If possible create an ODBC connection  and see if you are successful to
connect to the Access database.