Querying Access through Excel
 

Hi,

I have a spreadsheet that exports data into Access.
I used ADO/DAO to do this without a problem.

I am adding more functionality to the spreadsheet and want to be able
to tell if i am going to add duplicate records before they get added.
I want Excel to look at two columns in Access - "Year" and "Month" and
if they are the same as the year and month of the data that I am about
to enter I want a message to pop-up warning me that I could be
entering duplicate information.

I've tries the following:

sSQL = "SELECT * FROM Activity WHERE ([ReportingMonth]=1 AND
ReportingYear]=2004/2005)"
rs.Open sSQL, cn


MsgBox rs.RecordCount

If rs.RecordCount 0 Then MsgBox "duplication possible"

but the problem i come across is that rs.RecordCount displays "-1"
even when i have thousands of records that match the criteria.

Can anyone point out where i am going wrong.

many thanks in advance
chris dunigan

Querying Access through Excel
 

Without seeing your code, I'll make an educated guess. Move to the end of
the recordset before checking the record count. You may want to move back to
the beginning if you are going to do anything with the recordset besides
checking the count.

rs.MoveLast
rs.RecordCount

Troy

"Chris Dunigan" wrote in message
om...
Hi,

I have a spreadsheet that exports data into Access.
I used ADO/DAO to do this without a problem.

I am adding more functionality to the spreadsheet and want to be able
to tell if i am going to add duplicate records before they get added.
I want Excel to look at two columns in Access - "Year" and "Month" and
if they are the same as the year and month of the data that I am about
to enter I want a message to pop-up warning me that I could be
entering duplicate information.

I've tries the following:

sSQL = "SELECT * FROM Activity WHERE ([ReportingMonth]=1 AND
ReportingYear]=2004/2005)"
rs.Open sSQL, cn


MsgBox rs.RecordCount

If rs.RecordCount 0 Then MsgBox "duplication possible"

but the problem i come across is that rs.RecordCount displays "-1"
even when i have thousands of records that match the criteria.

Can anyone point out where i am going wrong.

many thanks in advance
chris dunigan

Querying Access through Excel
 

From the help file:

ADO 2.7 API Reference: RecordCount property: 'The property returns -1
when ADO cannot determine the number of records or if the provider or
cursor type does not support RecordCount.'

I'll guess you are using a forward only cursor and therefore
RecordCount is not supported.

Aside: you may need to take a look at the SQL, specifically how

ReportingYear=2004/2005

will be interpreted. Another guess, but if this is a 'text' column you
may need

ReportingYear='2004/2005'

If you want more than just guesses, I suggest you post your code,
schema and test data.

--

(Chris Dunigan) wrote in message . com...
Hi,

I have a spreadsheet that exports data into Access.
I used ADO/DAO to do this without a problem.

I am adding more functionality to the spreadsheet and want to be able
to tell if i am going to add duplicate records before they get added.
I want Excel to look at two columns in Access - "Year" and "Month" and
if they are the same as the year and month of the data that I am about
to enter I want a message to pop-up warning me that I could be
entering duplicate information.

I've tries the following:

sSQL = "SELECT * FROM Activity WHERE ([ReportingMonth]=1 AND
ReportingYear]=2004/2005)"
rs.Open sSQL, cn


MsgBox rs.RecordCount

If rs.RecordCount 0 Then MsgBox "duplication possible"

but the problem i come across is that rs.RecordCount displays "-1"
even when i have thousands of records that match the criteria.

Can anyone point out where i am going wrong.

many thanks in advance
chris dunigan