Excel -> Database

EasyUp Web Hosting

EasyUp Web Hosting

EasyUp Web Hosting
Company Rep
Joined
Mar 18, 2008
Messages
8,517
Reaction score
43
Location
Alberton
Hi Guys.

I have an excel file with data in. Obviously, only one person can work on it at a time, but I need to change it so that more than one person can work on it.

So, I thought of putting the data into a sql database and write like a small program the users can use to input the data with.

On catch, I ain't no developer. So, google and MyBB will be my source of info for this task. Do I have the right idea or should I be looking at something else?
 
Google docs? has collaboration.

But once excel starts showing its limitations, move to SQL+App
 
What version of Excel are you using?
I'm pretty sure 2007 allows multiple users to edit one file... Dunno how much of a mission it is to setup tho
 
Ya that can work. Just export all the data into a sql database (just post if u unsure) and create a stored procedure with something along the lines of:

INSERT INTO Details_tbl ( Name, Surname, Email, Phonenum, [Password])
VALUES ("name","surname","email",011512512,"pass")

Then all you have to do is call the procedure and input the data like:
sp_insert name,surname,email

and you should be good. Hope this helps a little
 
It's in excel 2003. I don't mind moving to excel 2007, but there is another catch, they shouldn't be able to delete record, only modify or add. Someone also said I should look at a MS access database?
 
poffle said:
Ya that can work. Just export all the data into a sql database (just post if u unsure) and create a stored procedure with something along the lines of:

INSERT INTO Details_tbl ( Name, Surname, Email, Phonenum, [Password])
VALUES ("name","surname","email",011512512,"pass")

Then all you have to do is call the procedure and input the data like:
sp_insert name,surname,email

and you should be good. Hope this helps a little
Click to expand...

I already got the data in a sql database, that was kinda easy, but the rest you are suggesting is Greek for me... :o
 
Can I upload the excel file somewhere and then you guys can see what I mean? It's a travel register with the basic info on it...
 
SharkBait said:
It's in excel 2003. I don't mind moving to excel 2007, but there is another catch, they shouldn't be able to delete record, only modify or add. Someone also said I should look at a MS access database?
Click to expand...

Not gonna get started on the MS Access recommendation ...

But a proper setup will be an HTML frontend linked to a database (MySQL). I do this at work. You just install a web server (Apache / Php / MySQL) on a designated PC and then everybody can access that.

A good developer will setup your database to have audit tables for edited / deleted rows. Also, you can draw flexible reports from the DB.

Not brain surgery and the cheapest route.
 
And this way you have no worries that users can delete records ;) since you will not be including a delete query.
 
cbrunsdonza said:
Not gonna get started on the MS Access recommendation ...

But a proper setup will be an HTML frontend linked to a database (MySQL). I do this at work. You just install a web server (Apache / Php / MySQL) on a designated PC and then everybody can access that.

A good developer will setup your database to have audit tables for edited / deleted rows. Also, you can draw flexible reports from the DB.

Not brain surgery and the cheapest route.
Click to expand...

Would've been my suggestion as well! You have speed, reliability and full control over who gets to do what. Reporting is a breeze as well as data manipulation...
 
you guys are really overcomplicating things. i dont think he wants an enterprise solution. just put your data in a table in access and run the upsizing wizard.

you can set the permissions in sql not to allow delete to anyone
 
SharkBait said:
Hi Guys.

I have an excel file with data in. Obviously, only one person can work on it at a time, but I need to change it so that more than one person can work on it.

So, I thought of putting the data into a sql database and write like a small program the users can use to input the data with.

On catch, I ain't no developer. So, google and MyBB will be my source of info for this task. Do I have the right idea or should I be looking at something else?
Click to expand...

I use Visual Studio, works perfect for me on on excel docs with out the use of SQL or mdb....... I use 10 users on I excel.... but with a sub program writen with C#.....
 
Jabberwocky said:
you guys are really overcomplicating things. i dont think he wants an enterprise solution. just put your data in a table in access and run the upsizing wizard.

you can set the permissions in sql not to allow delete to anyone
Click to expand...

+1

At the moment Excel works for the data part, but not the multiple users. Import the existing data into Access, set a few permissions and share the file. It will take a few minutes.

SQL servers are superior to Access….. but this is a simple single table application.
 
KobusDJ said:
+1

At the moment Excel works for the data part, but not the multiple users. Import the existing data into Access, set a few permissions and share the file. It will take a few minutes.

SQL servers are superior to Access….. but this is a simple single table application.
Click to expand...

Yap access is the best option but i wonder what he whats to do! Make a sub program to access excel or wants to access the same file!!
 
Hi Guys,

Thanx for the response. Went the access route. The fastest and easiest way for me. We decided not to put the old data in, cause the is no consistency with the fields and importing the data became a nightmare. So, we starting from scrats. The excel fill will get password protected and the 4 users that is going to use the database will each have there own username and password.

I have learned a lot, but am afraid I am going to forget it again as this should be the last time I do something like this at this company. :D

I created a table where all the new data will be stored and a form for the users to use with drop down menus, so now they can't use whatever they want. :p Even popup calendars to choose dates and everything. For a first time, I am very please with myself. Thanx for all your input.
 
MS Access is always the easiest for this sort of thing (small scale quick apps). The only problem with MS Access is it can be "too easy" in that people end up abusing it .

In alot of companies i've had to fix stuff at, there's usually a little "avalanche" of "mini applications" all over the place. Everyone and their grandmother can use MS Access and instead of getting proper IT support or an integrated solution, they end up writing their own stuff (which are not always efficient or work with the correct data).

In the end you get there and they have like 50 of these things (each having a different copy of the "real" data), and then some of them even use data from other Access apps!

It's when they start breaking [after the original authors left] where the paw paws start hitting the fans.

So MS Access is actually a -great- tool, but people tend to try to do too much with it. We already jokingly refer to companies using it as implementing "Excel ERP" or "Access ERP" ...ERP being your enterprise system like SAP/Oracle/SysPro etc etc . There's companies [who's not that small] running entirely off Excel spreadsheets....scary
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X