Export from Excel to Access but check 2 criteria to ensure no duplicates

Hi All,

As explained in the title I have a spreadsheet where you input your requewsted shifts for the week and then it is exported to access for the Roster manager to work out how many of each are available.

The code I have for the first part which is just a straight export to access works fine.

Please Login or Register  to view this content.

The code that isn't working for me at the moment is:

Please Login or Register  to view this content.

I am trying to tweak something saw elsewhere. any help is much appreciated.

Anything?

When you're accessing an Access database using SQL from Excel, you must encapsulate your variables. For a string variable you must put 'variable' , for a date variable you must put #datevariable#. Also, put a ; at the end of the command line. Finally, you need to pick a cell property. Range("A2") isnt anything. You probably want the .VALUE in cell A2. Oh and it's easier to refer to your sheet name in the range statement....

eg StrSql = "SELECT * FROM Table1 WHERE USERNAME='" & range("Sheet1!A2").value & "';"

Excellent. Will try to incorporate all that. Thank you very much.

Also....

I've never opened a record set using

Please Login or Register  to view this content.

I've always done it with...

Please Login or Register  to view this content.

and either

Please Login or Register  to view this content.

to read into a variable or

Please Login or Register  to view this content.

to read into a worksheet.

You'd then find the record count with either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

But just cos I didn't learn to do what you're doing that way doesn't mean it's wrong.

Thanks for the help. I am still struggling to get it to work.

Please Login or Register  to view this content.

Can I define x and y as integer even if they are dealing wit haccess record counts? I don't understand why it says object missing.

Thanks in advance

I can't see where you've declared this variable anywhere: dbOpenDynaset

Shouldn't it be cnn, not dbOpenDynaset?

Please Login or Register  to view this content.

This is not opening the recordset, it is 'saving' teh recordset so that I can carry out a rowcount and transform that into a number.

I think I am out of my depth here in all honesty. I have never crossed over into SQL and maybe this is too big a job for a relative 'noobie'.

try

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Disregard this reply, spotted what I couldn't see in the code.

I couldn't see it defined anywhere in the original so assumed it didn't need to be. Is this the best way, off the top of your head to check if the data already exists or can you point me in a direction of a better way to do so.

Just to clarify, if a username already has a request for given week it comes up error.

Thanks

I am getting an error 91 now. Variable nor with block variable not set.