read from access database with excel

[Megatronixs]

Hi all,

I got a macro runing that will write data to an access database. It will be kind of attendance calendar where people will insert the dates they are in and holidays and so on.
So far I got it to write the data from values in a few cells. Now I need also to read it back. The idea is that if someone wants to take a day off, she/he should check if no other has free on that day.
It could be that if one wants to check between 2 dates (from, to) and press a button, it should look in the access database and then show the persons who have free or other type of absence marked.
So the next step is to read the data.

This is what I got so far:

Please Login or Register  to view this content.

Any ideas how to get this going on?

Greetings.

[cytop]

You already have code to open/update the table, so a simple SELECT statement should do that...

Please Login or Register  to view this content.

If that returns any records, then someone has booked time off in that period.


Typed freehand - untested.

[Megatronixs]

Hi cytop,

where should I insert the above code?
still green in vba :-)

greetings.

[Megatronixs]

Hi,

Maybe the code could be changed to only read records and show them in excel starting from row 7 that have a "from" date in cell B4 and "To" date in C4. this way I could just return the data that some one wants to check.
Just 2 different macros.

Thanks in advance.

[Megatronixs]

Hi,

Finaly I found some macro in Google and managed to change some things to work out. The problem I have now, is that I want to pass some dates from the excel sheet so it will show only those records. How can I pass it from a cell to the SQL?
(please see the below code and let me know where I went wrong. I also get the following error: Compile error: can't find project or library)

Please Login or Register  to view this content.

[cytop]

It seems a little convoluted... For example, you could simply select those records and the fields you are interested in displaying...

Please Login or Register  to view this content.

And rather than copying individual fields from the recordset into an array and the array to the worksheet, you can simply copy directly from the resulting recordset to the worksheet...

Please Login or Register  to view this content.

You could make it into it's own procedure where you pass the variables such as StartDate and EndDate

Please Login or Register  to view this content.

and you can then use the passed variables for the query parameters.

Very brief code ignoring all related functionality like opening connections, declaring vars etc...

[Megatronixs]

Hi,

Is it possible to link the value to a cell ("K7") instead of using

Please Login or Register  to view this content.

Thanks in advance.

[Megatronixs]

Hi all,

I managed to solve it:

Please Login or Register  to view this content.

I had to change also the From to DFrom. I did move the field with the date to K3. I have a great (half) working tool now.

My next step will be that it will put all the retrived data in a calendar looking view.

Greetings and hope some one can make use of it.