Using Access from Excel

[AndrewKent]

Hi there,

I'm very new to using Access although I have been able to use Excel to a fairly high level. For a project I am about to undertake, I was looking to combine the two.

I am writing a program within Excel/Visual Basic that completes an application form to be printed and sent out. In addition to this, all information that is captured for the application, I want captured within a database. Previously, a simple Excel spreadsheet was used however it would seem Access would be better suited to this (as my user base is 50+ with potentially thousands of records).

My questions are:

Can my program (which will be used by 50+ users, all accessing it Read Only) be linked directly to an Access database? Therefore any application they complete, the details are svaed directly to the database?

Do I need to have Access installed on ever users pc or can Excel query the database directly without it.

I would like t add Add/Amend functionality to the Excel program, so that records could be created and existing records edited.

Would it be possible with multiple users? Ie, two people access the same record.

Any help would be fantastic,

Andy

 

[SydneyGeek]

Hi Andy,

It is definitely possible. Here is a tutorial covering much of what you need.
As for the other questions...
1. Excel can read and write to Access -- see tutorial
2. Excel users do not need Access installed to work with the data file
3. You can build routines to pull back and change specific records. You will need a unique ID in Excel for this.
4. Multiple users can use the system. They won't be able to simultaneously access the same record (they generally can't in Access either, if record locking is used)

Hope this gets you started

Denis

 

[Fazza]

Andy,

This seems like a duplicate of another thread http://www.mrexcel.com/forum/showthread.php?p=1725950

Best to keep to one. regards, Fazza

 

[AndrewKent]

Hi there guys,

Firstly, I didn't think it was a duplicate as in the first thread, I wasn't going to be using Access, just Excel, but I can see now they are fairly similar!

Secondly, thank you for sending on the Tutorial, it answered a few questions straight away, even from reading the first page. The database I want to build shouldn't be too complex although it will potentially house quite a lot of information, I'm Microsoft Office certified however I've not actually had any reason to use Access until now, is there anything I should watch out for when designing the database (which I'm certainly going to use the tutorial for)?

Also, it talks about no two users being able to access the same record. If that happened, surely I could build in coding that would return an "In Use" message?

Andy

 

[AndrewKent]

It also talks about ADO, is this included in Excel?

 

[Fazza]

Yes, you're right, Andy. I was getting a little confused as this thread seems to take off from where the other one ended up, with the work being done from Excel.

ADO is available from Excel. It is an underlying technology and can be used from Excel, Word, Outlook, etc.

There will be many examples and on line information. Like I posted in the other thread, although it is an mdb file, all can be done from VBA without Access.

Cheers, Fazza

 

[SydneyGeek]

Hi there guys,

Firstly, I didn't think it was a duplicate as in the first thread, I wasn't going to be using Access, just Excel, but I can see now they are fairly similar!

Hi Andy, Access and Excel aren't exactly similar -- they do different things but they do work together very well. To give a quick summary:

Excel is great for analysing datasets that don't cover more than one or two topics, and for analysing data across numerous columns (fields). But to pull together related information from several topics / tables using formulas will bog you down very quickly.
Access doesn't analyse across columns very well at all. But it excels (excuse the pun) at correlating lots of related data from many tables. Pulling that sort of stuff in a query is a snap. And it doesn't have the orw limits that Excel has.

The system I pointed you to (and others like it) will let many users update data, which you can slice and dice in Access and return to Excel for further analysis.

To get a further idea of Access, table design, and reporting with Excel, go to this link and download the first two resources referred to in the rigth column.

Secondly, thank you for sending on the Tutorial, it answered a few questions straight away, even from reading the first page. The database I want to build shouldn't be too complex although it will potentially house quite a lot of information, I'm Microsoft Office certified however I've not actually had any reason to use Access until now, is there anything I should watch out for when designing the database (which I'm certainly going to use the tutorial for)?

See the links above...

Also, it talks about no two users being able to access the same record. If that happened, surely I could build in coding that would return an "In Use" message?

Andy

I guess you could. Generally, your users will download data to Excel, edit it there, and return it to Access so they won't be editing the record in teh same place.

Also, you asked whether ADO ships with Excel. Check out the detail in the tutorials, it shows you how to reference the ADO libraries.

Denis

 

[AndrewKent]

Hi there,

SyndeyGeek, first of all, that tutorial was excellent, the coding wasn't too difficult to understand (I haven't seen the coding to link Excel to Access before) and it looks like this is the kind of thing I could use. A few follow up questions for you...

The tutorial talks about building the table in Excel and then creating an Access file with it. I would probably be building the database in Access (when I get it installed via a business request, I don't have it yet) so I presume I could still use some of the macros on a database that was built in Access rather than using the code to build it?

Secondly, the routine to edit one record used here, extracts the entire contents of the database, then edits one record. I want to extract one record, edit it, and send it back. I'm presuming this is an easy alteration?

And lastly, I'll probably have multiple tables in my database, the ID key being the link between tables (1 to many relationship, is this correct?) would it be easy to use this code to "update" multiple tables if nesseccary?

Many thanks,

Andy

 

[SydneyGeek]

The tutorial talks about building the table in Excel and then creating an Access file with it. I would probably be building the database in Access (when I get it installed via a business request, I don't have it yet) so I presume I could still use some of the macros on a database that was built in Access rather than using the code to build it?

That's right -- there is no need to create the database from Excel first. I added that so that people could see the possibilities, rather than as a compulsory step.

Secondly, the routine to edit one record used here, extracts the entire contents of the database, then edits one record. I want to extract one record, edit it, and send it back. I'm presuming this is an easy alteration?

Yes it's pretty easy to change. You need to build a query that extracts just one record (based on an ID), then edit and return it.

And lastly, I'll probably have multiple tables in my database, the ID key being the link between tables (1 to many relationship, is this correct?) would it be easy to use this code to "update" multiple tables if nesseccary?

Yes you can update a multi-table setup from Excel. I would suggest building a query in Access that already has the table joins in place, checking to see that it is editable, and push your Excel data to that query.

Denis

 

[AndrewKent]

I seem to be having a bit of bother asking it to bring back only one record based on the record ID. and I presume I'm going to have the same bother writing it back.

If worksheet("HUB").Range("a1").Value contains the record ID I wish to bring back, how do I get it to return that entire row within the table. And then once edited, load it back up.