Linking excel to access

[ItIsHardToProgram]

Hello every one!

It is a nice summer day today, wich makes me happy!

Ok, down to business... I am currently working with a consultant on a project, the project is simple, he has to link excel sheets to an access database, thus with the sheets that have been provided to him: Time sheets, Spending, and project sheets.

The Tables in the Db are the following:

Feuille de temps
Employee number #
Project number #
Sunday to saturday (# of hours)
Week involved [date]
Bank total (keeping track)

Employee
Employee number #
Employee name [string]
Salary / hour $
Function [string]
Autorisation (boolean) (to come)

Spendings
Date [date]
Description [string]
Project number (#)
Kilometers (#)
Representation ($)
Dinner ($)
Other ($)

Project
Project number #
Project Description [string]
Received so far $
Contractors $
R&D (%)


So he has all these fields to fill up, with information entered in an excel sheet, wich have cells that contain that information!


I have 2 or 3 questions:

First of all, as I am waiting on his part, I am wondering myself how hard is it to program such a "small beast". Basicaly, if you could get me started on how you would proceed to code this through VBA.

Secondly, I am wondering how much time would it take an expert programmer to do?

Thirdly, am I proceeding the wrong way????


Thanks for your insights, its greatly appreciated!




"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

What is the layout of your excel sheet ?
Have you tried to import it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Ok, all good questions, but quite hard to define without sending an actual template.


I want people to fill up these excel sheets as they would normaly, but by the use of a macro, to send all the information in the Database.

The layout is prety much as the fields go, for the time sheet, it is column / lines

Columns are prety much the fields name, and the information is entered accordingly.

Everything is to be sent, in a prety straight forward method. (Using a virtual table in VBA is a requirement I stated.)

Is that enough information for you?


For your second question

When you say import, I am not sure what you mean. I don't want the people to have to load access as the settings are not done on every computer. What I want the excel sheet to be doing is act as a front end for the back end that would be in the server.

Hope this helps, Thank you very much


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

ItIsHardToProgram,

ItIsHardToHelp when you don't know what it is you're working with.

It's REALLY important to know what your Workbook Front End looks like as far as what data elements are there in what kind of a format.

Is the format TABULAR?

Where does this workbook reside?

Do multiple users enter data into this workbook?

What is the process for the back end to acquire data from the workbook?

Please answer ALL these questions, clearly, concisely and completely.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I have answers to all these questions, im not sure how to formulate in a clear, and concise way.

It's REALLY important to know what your Workbook Front End looks like as far as what data elements are there in what kind of a format.

Is the format TABULAR?

Yes. (I believe this mean is it a table (X,Y) [columns + lines]

Its quite simple actualy it goes like this: (I am not good with TGML )


I will only talk about the timesheet for now.

First of all: theres the employee name, employee number and Date of the week , wich is text, in a fused cell.

Then there is columns, they go like this:

[tt]Projet Number[/tt] [tt]Projet Name[/tt] [tt]Monday[/tt] [tt]tuesday[/tt] [tt]Wenesday[/tt] [tt]Thursday[/tt] [tt]Friday[/tt] [tt]Saturday[/tt] [tt]Sunday[/tt].

There are 2 types of entry (in lines)

Either Normal or R&D, wich repeat every two line, up to 20 entries per timesheet.

Normal
R&D
next entry
Normal
R&D
next entry
Normal
R&D
next entry

Where does this workbook reside?

There will be 1 workbook per computer, 1 macro per workbook, wich communicates with a backend (access) located in a windows 2003 server.

Do multiple users enter data into this workbook?

Multiple user will enter weekly data into the backend, through the excel to access link process.

What is the process for the back end to acquire data from the workbook?

This is why I am hireing some one, he comes up with that process, it needs to back and forth, retrieving data and inputting data.

And this is what I am asking you guys, what process to use for my needs, and how much time would it take you to come up with something like that.

The backend is formed with the fields and tables I mentioned.

I hope this helps you understand my questions





"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

You ought to rethink this design. It might be better to have it all done on the server, rather than depending on a bunch of clients to push the data up to the client.

First of all: theres the employee name, employee number and Date of the week , wich is text, in a fused cell.

What is a fused cell? Is it MERGED CELLS?

Normal
R&D
next entry
Normal
R&D
next entry

are there empty rows between entiries?



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

No empty rows between entries,

=$ fused cell is merged cell, my bad french deviation, tranlating can sometime be an hassle!


For having it on the server, I am afraid that if I put it on the server, and people have different client version, it might cause problems, as some computers use from 2003 to 2007 office suit. + it won't give us problem as to "multi-user managing".

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I'm suggesting NOT using Excel, but a server side application for the front end, as another consideration.

How many Excel clients (users) will there be, pushing out data?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Around 20,

The reason using excel was thrusted forward is because it is the only thing, at most, that users would understand, plus if the link could be done, it would be easy to manage....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Why is
[tt]
employee name, employee number and Date of the week
[/tt]
in a merged cell?

Each of these data elements are necessary to the process. Why bury them and make it difficult to get to? You are shooting yourself in the foot.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Nonono, not the same merge cell, each field has a merged cell accordingly, its to give room while entering ...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

...to give room while entering

Why not adjust the width of ONE column for each of the elements in question?

Why does they have to be merged?

Merged cells often cause problems.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

They do not have to be merged, if merged cells cause problem I can certainly unmerge them, without any problem! It will some what not look as nice!



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

SO, overall, what is your opinion of this, faisability and time to do so?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Here's an example of a "timesheet" on a Mainframe screen...
[tt]
CLOCK EMPLOYEE NAME DATE SCROLL? APPROVALS
99999 SkipVought 041709 N SUPV: 88888 04/17 08:25
SHIFT: 1 WS: 508 DEPT: XX REPORTS TO: EWW :
DLN/ COM EMPL: 99999 04/17 07:21
TC SUNDRY BUS WO TITLE SAT SUN MON TUE WED THU FRI PAID
ASSIGNED WORKWEEK 00 00 08 08 08 08 08
F FAM. ILLNESS 8.0 8.0
999 8.0 8.0
V VACATION 8.0





STRAIGHT TIME 8.0 8.0 8.0 8.0 8.0 40.0
[/TT]
I post this to point out several things.

1. The User-related data is in one place ABOVE the time charging.
2. The Process Date is in one place ABOVE the time charging.
3. The detail data, charging per day is in multiple rows
4. Summary data at the bottom.

Since each user will have their own client (workbook), there is no reason to have the ID, Name, Process Date on each detail row.

Here's a possible process.

1. The workbook is opened.
2. The Open_Workbook detects the network UserID.
3. The UserID & Process Date is filled by a procedure. The UserName is accessed from the Employee DB using the UserID, and place in the sheet.
4. The Time DB is accessed, using the Process Date. If there is data for that date, the detail part of the form is populated with that data.

now in your system there are TWO charge rows.

5. The user Adds, Changes, Deletes TIME VALUES in each of these rows.
6. Error checking occurs, if any.
7. The user must make an overt UPDATE, like hit an Update Button, to send the data to the database.

Our Process Date is always a Friday, the last day of the work week.
[tt]
=INT((TODAY())/7)*7+6
[/tt]
will calculate the Process date accordingly.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Wich is somewhate near to what we are trying to achieve here.

The problem with having the auto-populate is that you need someone to manage the DB, wich they most likely won't have.

I will do something close to what you are saying, I really will do all of that myself, the only thing I wanted some one else to do is the link, between the sheet to the DB, wich is what the person is actualy doing.

Do you see any ay to "surf through" the data itself, using the excel spreadsheet, or would that be too complicated and heavy

Secondly, how much time, using the tools I mentioned, would you personaly take to come up with it.




"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

The problem with having the auto-populate is that you need someone to manage the DB, wich they most likely won't have.

If there is a problem with this, then this system is at risk!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}