Emplyees and Projects database

[LordLoki]

Hi Gina,

Thanks for that link read it and its very nice. Will try to follow that rules and make my work more readable for others. There are some questions popping up but i will google a bit before i come back to you for them.

One i have to ask now cause we are building a small access table at work and im curious if we making sth wrong.

You write "Data should never be Deleted,period"
We have to write Data every Hour and the input comes as excel it always has the data of the whole day 0:00 - 23:59 and we need data from start of day until now in hourly intervalls.

At the moment we have a script that fires every hour when the new data arrives. It deletes the data from the database and writes the data new cause its easier then to fiddle out which data we already have and append the rest.

Is that a problem and if so why.

Thanks for your help

 

[GinaWhipp]

I have a saying...

The surest way to find out of you need data is to delete it!

The reason not for delete is historical. The kind of databases I build require historical and even if they didn't I don't delete. Why? Because it never fails, Client clicks delete and then calls me because it was mistake and they really need that data back. Well, your way, they are just out of luck, my way, I look like super woman becuase it's still there. Now, that said, you sound as if you are using the database to *wash* the data, if that is so then by all means delete and replace. However, doing for ease is not a good reason, at least, not for me. I would just write a routine to identify the existing against the new and handle the records.

If it's size you're worried about then it might be time to think SQL Server.

 

[LordLoki]

Its not size cause we are writing the same data we deleted just with the new intervall. The problem is not only getting the latest data and write the newest that would be easy to do. We would also need to check if the latest existing data is the same as in the new source sheet. But i think that is going too far for discussion here. I was just wondering if access maybe has a problem with constantly deleting data and write it new

 

[GinaWhipp]

Only one... you automatically get the space back upon deletion of data, you have to run Compact & Repair.

 

[Immortal]

This definitely helped me in understanding how a SQL code works and how I can set a criteria to a query using forms which would be really useful. I will be digging in SQL later to maximize the potential of my databases.
And the forum should feature your video...

 

[Immortal]

Can i have some advice here ?

I have a MemberProjectT which has 2 fields (in addition to ID) those fields are memberID and ProjectName.
This table has all the entries of members working on projects
I want to be able to remove a record from this table using this command
where Y is the ID of the member in the form and List23 has the Project name selected.

"DELETE [MemberProjectT.*] FROM MemberProjectT WHERE MemberID = " & Y & " And ProjectName = " & 
[List23].[Column](0) & " "

It wants me to enter a parameter value for the Project name, when I enter a valid project name it works!

 

[LordLoki]

Hi Immortal,

It should work with this:

"DELETE [MemberProjectT.*] FROM MemberProjectT WHERE MemberID = " & Y & " And ProjectName = " & List23.Column(0, List23.ListIndex) & " "

Where the 0 in "List23.Column(0, List23.ListIndex)" is the column number of the Projectname

The list23.listindex always returns the rownumber of the selected Item.
If you have a Listbox with Multiselect this would not work

Greets
Loki

 

[Immortal]

Still prompting me to enter a parameter value
What is bothering is that I tried outputting both values to msgbox and they worked right!

 

[GinaWhipp]

Hmm, is Project Name in the second column? If so, try this...

"DELETE [MemberProjectT.*] FROM MemberProjectT WHERE MemberID = " & Y & " And ProjectName = " &[List23].[Column](1) & " "

Please consider giving the Controls on your Forms *real* names. Here's why...
http://regina-whipp.com/blog/?p=102

 

[Immortal]

No it appears it is not, I thought that might be the case if the hidden ID column is considered 0, but i tried using msgbox and it was null. It should be
[List23].[Column](1)

I even enter a project name in the parameter and it works fine!
This is just my prototype base where I test things out, I would consider comprehensive names for objects if I were to be working on a real database.

Edit:
Gaaah, it was so stupid.. apparently I had to enclose it with ' " because it was a string.
it worked like this

"DELETE [MemberProjectT.*] FROM MemberProjectT WHERE MemberID = " & Y & " And ProjectName = '" & 
[List23].[Column](0) & "' "

 

[GinaWhipp]

Don't feel bad we ALL missed that! :banghead: