help!!!!!

[mswantek]

First, I have gotten much great advise from the excel portion of this site, Now I am progressing to Access. My problem is this:

I have created a VBA driven form that workflows to different people to collect data. Up until now I have stored all the info into a different excel form to be used as my database. This "database" is opened, written to , and closed within the vba code without the user knowing it. I should mention too that the files are being used over a network at work. The problem I am facing now is that the "database"(excel ) is on a drive that the user needs to have acess to in order to write to it within the vba code. Is there a way that MS Access could be used to overcome this? I am new to Access and want to know if it is worth persuing.

 

[SydneyGeek]

If you're trying to set up a multiuser environment, Access is better than Excel -- better security, more control over data entry.
I'm not completely sure what you need to do, but you could convert the hidden "Database" to an Access table, and use forms within Access to set up the data entry. Access is also a great reporting tool when you get used to it.
Once you're happy that the forms work as required, you can split the database. Make sure that the backend goes onto a shared drive (nominate its location when prompted by the wizard) and then put a copy of the front end on the desktop or C: drive of every user. When they open the database they will all be writing to the backend. If you don't go over about 5 concurrent users, this should do everyhing you need.

Very high-level view, but I hope this helps.

 

[mswantek]

Syd,

Thank you so much!! I never could figure out how the whole package worked. My issue though is (and I am making an assumption here) that every user you refer to would have to have
Access installed on their computer. What if they dont? Is there a way to have a backend database of Access and a front end of Excel? I need to find something (short of a web based system) that will accomplish a task of multiple users, very few Access licences, and all users have Excel. It seems as if I am in an impossible situation.
Mike

 

[SydneyGeek]

Mike,

Sorry to say but if you need to cut down on Access licenses, using it as your front end isn't really an option. What's the current problem with the Excel solution -- does it work for you but not others?
If that's the issue, maybe you need to look at setting write permissions for users on the shared folder. Check with your IT guys (unless you ARE the IT guy!! :wink: ) and see what they can come up with.

 

[brettvba]

Also my 2 cents, you can use Access to store your data and write and retrieve with Excel via ADO, it works great but as I said my 2 cents>

Brett

 

[mswantek]

Also my 2 cents, you can use Access to store your data and write and retrieve with Excel via ADO, it works great but as I said my 2 cents>

Brett

Brett,

Thanks for your two cents... Every penny helps . I will investigate the ADO way thanks. If you know of any good links about it please forward them.
I am assuming (here I go again) that Excel would collect the data and then send it to the database in Access?

 

[mswantek]

Mike,

Sorry to say but if you need to cut down on Access licenses, using it as your front end isn't really an option. What's the current problem with the Excel solution -- does it work for you but not others?
If that's the issue, maybe you need to look at setting write permissions for users on the shared folder. Check with your IT guys (unless you ARE the IT guy!! :wink: ) and see what they can come up with.

Syd,

There is nothing wrong with the current way for anybody EXCEPT, I don't know if everybody has access to the shared drive that we use........

 

[Smitty]

Type "share an access database on a network" in Access help. It should answer your network questions.

If you have Office 2000 Developer's Edition, there is a "Package & Deployment" wizard that will allow you to create a run-time version of your database. That allows users who do not have Access to still "access" the database.

You may also be able to download it from Microsoft.

HTH,

Smitty

 

[mswantek]

Thanks Smitty,

I will try to do that!!!

Mike

 

[bat17]

The problem I am facing now is that the "database"(excel ) is on a drive that the user needs to have acess to in order to write to it within the vba code. Is there a way that MS Access could be used to overcome this? I am new to Access and want to know if it is worth persuing.

In Access all users will still need permission to the drives and quite possibly higher permissions than when using Excel as Access requires Create and Delete permission as well as Read/write.

HTH

Peter