I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they would like to be able to query but I do not want to use excel as a database as I don't think it is fit for that purpose and am considering using Access. [BTW, I know Access has its shortcomings but there is zero budget available and Access already on friend's PC]
我正在为一个朋友构建一个小型应用程序，他们希望能够使用Excel作为前端。(UI基本上是Excel中的userforms)。他们在Excel中有很多数据他们想要查询，但我不想用Excel作为数据库，因为我认为它不适合这个目的，我正在考虑使用Access。[顺便说一句，我知道Access也有它的缺点，但是在friend的PC上没有可用的预算和访问权限]
To summarise, I am considering dumping a bunch of data into Access and then using Excel as a front end to query the database and display results in a userform style environment.
综上所述，我正在考虑将大量数据转储到Access中，然后使用Excel作为前端来查询数据库并在userform样式的环境中显示结果。
Questions:
问题:
- How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
- 使用ADO / DAO链接到Excel访问有多容易?它在功能上是很有限的还是我能有创意?
- Do I pay a performance penalty (vs.using forms in Access as the UI)?
- 我是否需要支付性能惩罚(使用表单作为UI)?
- Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
- 假设数据库总是使用Excel VBA中的ADO / DAO命令进行更新，这是否意味着我可以让多个Excel用户使用同一个访问数据库，而不会遇到任何并发问题?
- Any other things I should be aware of?
- 我还需要注意什么?
I have strong Excel VBA skills and think I can overcome Access VBA quite quickly but never really done Excel / Access link before. I could shoehorn the data into Excel and use as a quasi-database but that just seems more pain than it is worth (and not a robust long term solution)
我有很强的Excel VBA技能，我认为我可以很快地克服Access VBA，但之前从未真正做过Excel / Access链接。我可以将数据硬塞进Excel中，并将其用作准数据库，但这似乎比它的价值更痛苦(而不是一个健壮的长期解决方案)
Any advice appreciated.
任何建议表示赞赏。
Alex
亚历克斯
12 个解决方案
#1
39
I'm sure you'll get a ton of "don't do this" answers, and I must say, there is good reason. This isn't an ideal solution....
我肯定你会得到很多“不要做这个”的答案，我必须说，这是有原因的。这不是一个理想的解决方案....
That being said, I've gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn't talk around it.
话虽如此，我以前也有过类似的经历，主要是因为这份工作明确规定了这是一项苛刻的要求，我无法谈论它。
Here are a few things to consider with this:
以下是一些需要考虑的事情:
How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
使用ADO / DAO链接到Excel访问有多容易?它在功能上是很有限的还是我能有创意?
It's fairly straitforward. You're more limited than you would be doing things using other tools, since VBA and Excel forms is a bit more limiting than most full programming languages, but there isn't anything that will be a show stopper. It works - sometimes its a bit ugly, but it does work. In my last company, I often had to do this - and occasionally was pulling data from Access and Oracle via VBA in Excel.
这是相当straitforward。比起使用其他工具，您更有局限性，因为VBA和Excel表单比大多数完整的编程语言更有局限性，但是没有任何东西会成为显示的障碍。它起作用了——有时有点丑，但确实起作用了。在我的上一家公司，我经常不得不这样做——偶尔会从Access和Oracle中提取数据，通过Excel中的VBA。
Do I pay a performance penalty (vs.using forms in Access as the UI)?
我是否需要支付性能惩罚(使用表单作为UI)?
My experience is that there is definitely a perf. penalty in doing this. I never cared (in my use case, things were small enough that it was reasonable), but going Excel<->Access is a lot slower than just working in Access directly. Part of it depends on what you want to do....
我的经验是肯定有一个人。在这样做。我从不关心(在我的用例中，事情是足够小的，它是合理的)，但是使用Excel<->访问要比直接使用Access慢得多。的一部分,这取决于你想做什么....
In my case, the thing that seemed to be the absolute slowest (and most painful) was trying to fill in Excel spreadsheets based on Access data. This wasn't fun, and was often very slow. If you have to go down this road, make sure to do everything with Excel hidden/invisible, or the redrawing will absolutely kill you.
在我的例子中，看起来最慢(也是最痛苦)的事情是尝试根据访问数据填写Excel电子表格。这并不有趣，而且通常很慢。如果你必须沿着这条路走下去，一定要用Excel隐藏/看不见的东西做任何事情，否则重新画就会杀了你。
Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
假设数据库总是使用Excel VBA中的ADO / DAO命令进行更新，这是否意味着我可以让多个Excel用户使用同一个访问数据库，而不会遇到任何并发问题?
You're pretty much using Excel as a client - the same way you would use a WinForms application or any other tool. The ADO/DAO clients for Access are pretty good, so you probably won't run into any concurrency issues.
您使用Excel作为客户端，就像使用WinForms应用程序或其他工具一样。用于访问的ADO/DAO客户机非常好，因此您可能不会遇到任何并发问题。
That being said, Access does NOT scale well. This works great if you have 2 or 3 (or even 10) users. If you are going to have 100, you'll probably run into problems. Also, I tended to find that Access needed regular maintenance in order to not have corruption issues. Regular backups of the Access DB are a must. Compacting the access database on a regular basis will help prevent database corruption, in my experience.
话虽如此，访问的规模并不大。如果您有2个或3个(甚至10个)用户，那么这将非常有用。如果你有100个，你可能会遇到问题。而且，我倾向于发现，访问需要定期维护，以避免出现腐败问题。必须定期备份访问数据库。根据我的经验，定期压缩访问数据库将有助于防止数据库损坏。
Any other things I should be aware of?
我还需要注意什么?
You're doing this the hard way. Using Excel to hit Access is going to be a lot more work than just using Access directly.
你这样做很困难。使用Excel点击访问将比直接使用访问要多得多。
I'd recommend looking into the Access VBA API - most of it is the same as Excel, so you'll have a small learning curve. The parts that are different just make this easier. You'll also have all of the advantages of Access reporting and Forms, which are much more data-oriented than the ones in Excel. The reporting can be great for things like this, and having the Macros and Reports will make life easier in the long run. If the user's going to be using forms to manage everything, doing the forms in Access will be very, very similar to doing them in Excel, and will look nearly identical, but will make everything faster and smoother.
我建议您查看Access VBA API——它的大部分与Excel相同，因此您将得到一个小的学习曲线。不同的部分使这更容易。您还将拥有访问报告和表单的所有优势，这些优势比Excel中的数据更加面向数据。报告对于这样的事情是很好的，拥有宏和报告从长远来看会使事情变得更容易。如果用户要使用表单来管理一切，那么在Access中执行表单将非常非常类似于在Excel中执行这些操作，并且看起来几乎相同，但是会使一切变得更快、更流畅。
#2
15
I do this all the time. If you're using ADO, you're not really using Access, but Jet, the underlying database. That means anybody with Excel can use the app - Access not required. Oh I should mention, the place I work bought a bunch of Office Small Business licenses - no Access. Prior to working here, I would have assumed that anyone who had Excel would also have Access. Not so.
我一直这么做。如果您正在使用ADO，那么您实际上并不是在使用Access，而是使用底层数据库Jet。这意味着任何使用Excel的人都可以使用这个应用程序——不需要访问。哦，我得说，我工作的地方买了一堆办公小型企业许可证——没有权限。在这里工作之前，我假设任何有Excel的人都有访问权限。不是这样的。
I create one class for every table in Access. I very rarely run queries through ADO, instead I keep that logic in the class modules. I read in with a SELECT statement and write out with and UPDATE or INSERT using the Execute method of the ADODB.Connection object.
我为访问的每个表创建一个类。我很少通过ADO运行查询，而是将该逻辑保存在类模块中。我使用ADODB的Execute方法读取SELECT语句，并使用和更新或插入。连接对象。
See http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/
参见http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/
if you want to see how I set up my code.
如果你想知道我是如何设置代码的。
To answer your questions: It will be a small learning curve for you if you already know Excel VBA, but there will be some learning to do; you will pay a performance penalty over doing it all in Access, but it's not that bad and only you can decide if it's worth it; and you can have multiple people accessing the database.
回答你的问题:如果你已经知道Excel VBA，这将是一个小的学习曲线，但是会有一些学习;你会在访问过程中付出性能代价，但这并不是那么糟糕，只有你自己才能决定是否值得;你可以让多人访问数据库。
#3
9
Just skip the excel part - the excel user forms are just a poor man's version of the way more robust Access forms. Also Access VBA is identical to Excel VBA - you just have to learn Access' object model. With a simple application you won't need to write much VBA anyways because in Access you can wire things together quite easily.
只要跳过excel部分——excel用户表单只是一个穷人版的更健壮的访问表单。此外，Access VBA与Excel VBA是相同的——您只需学习Access的对象模型。使用一个简单的应用程序，您不需要编写太多的VBA，因为在访问中，您可以很容易地将事物连接在一起。
#4
8
If the end user has Access, it might be easier to develop the whole thing in Access. Access has some WYSIWYG form design tools built-in.
如果最终用户有访问权限，那么在Access中开发整个东西可能会更容易。Access内置了一些WYSIWYG表单设计工具。
#5
7
Unless there is a strong advantage to running your user form in Excel then I would go with a 100% Access solution that would export the reports and data to Excel on an ad-hoc basis.
除非在Excel中运行用户表单有很大的优势，否则我将采用100%访问解决方案，该方案将报告和数据导出到Excel中。
From what you describe, Access seems the stronger contender as it is built for working with data:
you would have a lot more tools at your disposal to solve any data problems than have to go around the limitations of Excel and shoehorn it into becoming Access...
根据您的描述，Access似乎是一个更强大的竞争者，因为它是为处理数据而构建的:您可以使用更多的工具来解决任何数据问题，而不需要绕过Excel的限制，把它硬塞进Access中……
As for your questions:
至于你的问题:
-
Very easy. There have been some other questions on SO on that subject.
See for instance this one and that one.非常容易。在这个问题上还有其他问题。比如这个和那个。
-
Don't know, but I would guess that there could be a small penalty.
The biggest difficulty I see is trying to get all the functionalities that Access gives you and re-creating some of these in Excel.我不知道，但我猜可能会有一个小处罚。我看到的最大的困难是尝试获得Access提供的所有功能，并在Excel中重新创建其中的一些功能。
-
Yes, you can have multiple Excel users and a single Access database.
Here again, using Access as a front-end and keeping the data in a linked Access database on your network would make more sense and it's easy as pie, there's even a wizard in Access to help you do that: it's just 1 click away.是的，您可以有多个Excel用户和一个访问数据库。同样，使用Access作为前端并将数据保存在网络上的链接访问数据库中会更有意义，而且非常简单，甚至还有一个向导可以帮助您做到这一点:只需单击1下即可。
Really, as most other people have said, take a tiny bit of time to get acquainted with Access, it will save you a lot of time and trouble.
You may know Excel better but if you've gone 80% of the way already if you know VBA and are familiar with the Office object model.
实际上，正如大多数人所说，花一点时间来了解Access，它将为您节省大量的时间和麻烦。您可能更了解Excel，但是如果您已经了解VBA并熟悉Office对象模型，那么您已经了解了80%的内容。
Other advantages of doing it in Access: the Access 2007 runtime is free, meaning that if you were to deploy to app to 1 or 30 PC it would cost you the same: nothing.
You only need one full version of Access for your development work (the Runtime doesn't have the designers).
在Access中做它的其他好处是:Access 2007运行时是免费的，这意味着如果你要将应用程序部署到1或30个人电脑上，那么你的花费是一样的:没有。开发工作只需要一个完整的访问版本(运行时没有设计器)。
#6
3
It really depends on the application. For a normal project, I would recommend using only Access, but sometimes, the needs are specific and an Excel spreadsheet might be more appropriate.
这取决于应用程序。对于一个正常的项目，我建议只使用Access，但是有时候，需要是特定的，Excel电子表格可能更合适。
For instance, in a project I had to develop for a former employer, the need was to give access to different persons on forms(pre-filled with some data, different for each person) and have them complete them, then re-import the data.
例如，在我为以前的雇主开发的一个项目中，需要对表单上的不同人进行访问(预先填写一些数据，每个人的数据不同)，并让他们完成它们，然后重新导入数据。
Since the form was using heavy number crunching, it made more sense to build it in Excel.
由于表单使用了大量的数字运算，所以用Excel构建更有意义。
The Excel workbooks for the different persons were built from a template using VBA, then saved in a proper location, with the access rights on the folder.
针对不同人员的Excel工作簿是从使用VBA的模板构建的，然后保存在适当的位置，并具有文件夹上的访问权限。
All workbooks were attached as External tables to the workbooks, using named ranges. I could then query the workbooks from the Access Application. All administrative stuff was made from the db, but the end users only had access to their respective workbook.
所有工作簿都使用命名范围作为外表附加到工作簿上。然后我可以从访问应用程序查询工作簿。所有的管理内容都是由db来完成的，但是最终用户只能访问他们各自的工作簿。
Developping an Excel/Access application this way was a pleasant experience and the UI was more user-friendly than it would have been using Access.
以这种方式开发Excel/Access应用程序是一种愉快的体验，与使用Access相比，UI更加友好。
I have to say that in this case, it would have taken a lot more time doing it in Access than it took using Excel. Also, the Application Object Model seems better though in Excel than in Access.
我不得不说，在这种情况下，在Access中操作要比使用Excel花费更多的时间。而且，在Excel中应用程序对象模型似乎比在Access中更好。
If you plan to use Excel as a front-end, do not forget to lock all the cells, but the editable ones and don't be affraid to use masked rows and columnns (to construct output tables for the access database, to perform intermediate calculations, etc).
如果您打算使用Excel作为前端，请不要忘记锁住所有的单元格，但是要锁住可编辑的单元格，不要害怕使用掩码行和列(为访问数据库构造输出表，执行中间计算，等等)。
You should also turn off autocalculation while importing data.
您还应该在导入数据时关闭自动计算。
#7
2
It's quite easy and efficient to use Excel as a reporting tool for Access data. A quick "non programming" approach is to set a List or a Pivot Table, linked to your External Data source. But that's out of scope for *.
A programmatic approach can be very simple:
使用Excel作为访问数据的报告工具非常简单和有效。一个快速的“非编程”方法是设置一个列表或一个数据透视表，链接到您的外部数据源。但这超出了*的范围。方案方法可以非常简单:
strProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";"
Set cnn = New ADODB.Connection
cnn.Open strProv
Set rst = New ADODB.Recordset
rst.Open strSql, cnn
myDestRange.CopyFromRecordset rst
That's it !
就是这样!
#8
1
Given the ease of use of Access, I don't see a compelling reason to use Excel at all other than to export data for number crunching. Access is designed to easily build data forms and, in my opinion, will be orders of magnitude easier and less time-consuming than using Excel. A few hours to learn the Access object model will pay for itself many times over in terms of time and effort.
考虑到访问的易用性，我认为除了导出数据进行数字处理之外，使用Excel根本没有什么令人信服的理由。Access的设计目的是容易地构建数据表单，在我看来，它将比使用Excel更简单、更省时。学习访问对象模型的几个小时将在时间和精力上为自己付出很多倍的代价。
#9
1
I did it in one project of mine. I used MDB to store the data about bills and used Excel to render them, giving the user the possibility to adapt it.
我在一个项目中做过。我使用MDB存储账单数据，并使用Excel呈现账单数据，使用户可以对其进行修改。
In this case the best solution is:
在这种情况下，最好的解决办法是:
-
Not to use any ADO/DAO in Excel. I implemented everything as public functions in MDB modules and called them directly from Excel. You can return even complex data objects, like arrays of strings etc by calling MDB functions with necessary arguments. This is similar to client/server architecture of modern web applications: you web application just does the rendering and user interaction, database and middle tier is then on the server side.
不要在Excel中使用任何ADO/DAO。我在MDB模块中实现了所有的公共功能，并直接从Excel中调用它们。通过调用具有必要参数的MDB函数，甚至可以返回复杂的数据对象，如字符串数组等。这类似于现代web应用程序的客户机/服务器架构:您的web应用程序只执行呈现和用户交互，然后数据库和中间层在服务器端。
-
Use Excel forms for user interaction and for data visualisation.
使用Excel表单进行用户交互和数据可视化。
-
I usually have a very last sheet with some names regions for settings: the path to MDB files, some settings (current user, password if needed etc.) -- so you can easily adapt your Excel implementation to different location of you "back-end" data.
我通常有最后一个表，其中包含一些设置的名称区域:MDB文件的路径、一些设置(如果需要的话，是当前用户、密码等)——因此您可以轻松地将您的Excel实现调整为您的“后端”数据的不同位置。
#10
0
To connect Excel to Access using VBA is very useful I use it in my profession everyday. The connection string I use is according to the program found in the link below. The program can be automated to do multiple connections or tasks in on shot but the basic connection code looks the same. Good luck!
使用VBA来连接Excel是非常有用的，我每天都在我的职业中使用它。我使用的连接字符串是根据下面链接中找到的程序。该程序可以自动执行多个连接或任务，但基本的连接代码看起来是一样的。好运！
http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao
#11
0
It Depends how much functionality you are expecting by Excel<->Acess solution. In many cases where you don't have budget to get a complete application solution, these little utilities does work. If the Scope of project is limited then I would go for this solution, because excel does give you flexibility to design spreadsheets as in accordance to your needs and then you may use those predesigned sheets for users to use. Designing a spreadsheet like form in Access is more time consuming and difficult and does requires some ActiveX. It object might not only handling data but presenting in spreadsheet like formates then this solution should works with limited scope.
它取决于您期望Excel<->Acess解决方案提供多少功能。在许多情况下，如果您没有获得完整应用程序解决方案的预算，那么这些小实用程序就可以工作了。如果项目的范围是有限的，那么我将采用这个解决方案，因为excel确实可以根据您的需要灵活地设计电子表格，然后您可以使用那些预先设计好的表单供用户使用。在访问中设计类似表单的电子表格更费时、更困难，而且需要一些ActiveX。该解决方案不仅可以处理数据，而且还可以在表格中显示，这样的解决方案应该适用于有限的范围。
#12
-1
You could try something like XLLoop. This lets you implement excel functions (UDFs) on an external server (server implementations in many different languages are provided).
你可以试试XLLoop。这使您可以在外部服务器上实现excel函数(提供了许多不同语言的服务器实现)。
For example you could use a MySQL database and Apache web server and then write the functions in PHP to serve up the data to your users.
例如，您可以使用MySQL数据库和Apache web服务器，然后使用PHP编写函数向用户提供数据。
BTW, I work on the project so let me know if you have any questions.
顺便说一句，我在做这个项目，如果你有任何问题，请告诉我。
#1
39
I'm sure you'll get a ton of "don't do this" answers, and I must say, there is good reason. This isn't an ideal solution....
我肯定你会得到很多“不要做这个”的答案，我必须说，这是有原因的。这不是一个理想的解决方案....
That being said, I've gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn't talk around it.
话虽如此，我以前也有过类似的经历，主要是因为这份工作明确规定了这是一项苛刻的要求，我无法谈论它。
Here are a few things to consider with this:
以下是一些需要考虑的事情:
How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
使用ADO / DAO链接到Excel访问有多容易?它在功能上是很有限的还是我能有创意?
It's fairly straitforward. You're more limited than you would be doing things using other tools, since VBA and Excel forms is a bit more limiting than most full programming languages, but there isn't anything that will be a show stopper. It works - sometimes its a bit ugly, but it does work. In my last company, I often had to do this - and occasionally was pulling data from Access and Oracle via VBA in Excel.
这是相当straitforward。比起使用其他工具，您更有局限性，因为VBA和Excel表单比大多数完整的编程语言更有局限性，但是没有任何东西会成为显示的障碍。它起作用了——有时有点丑，但确实起作用了。在我的上一家公司，我经常不得不这样做——偶尔会从Access和Oracle中提取数据，通过Excel中的VBA。
Do I pay a performance penalty (vs.using forms in Access as the UI)?
我是否需要支付性能惩罚(使用表单作为UI)?
My experience is that there is definitely a perf. penalty in doing this. I never cared (in my use case, things were small enough that it was reasonable), but going Excel<->Access is a lot slower than just working in Access directly. Part of it depends on what you want to do....
我的经验是肯定有一个人。在这样做。我从不关心(在我的用例中，事情是足够小的，它是合理的)，但是使用Excel<->访问要比直接使用Access慢得多。的一部分,这取决于你想做什么....
In my case, the thing that seemed to be the absolute slowest (and most painful) was trying to fill in Excel spreadsheets based on Access data. This wasn't fun, and was often very slow. If you have to go down this road, make sure to do everything with Excel hidden/invisible, or the redrawing will absolutely kill you.
在我的例子中，看起来最慢(也是最痛苦)的事情是尝试根据访问数据填写Excel电子表格。这并不有趣，而且通常很慢。如果你必须沿着这条路走下去，一定要用Excel隐藏/看不见的东西做任何事情，否则重新画就会杀了你。
Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
假设数据库总是使用Excel VBA中的ADO / DAO命令进行更新，这是否意味着我可以让多个Excel用户使用同一个访问数据库，而不会遇到任何并发问题?
You're pretty much using Excel as a client - the same way you would use a WinForms application or any other tool. The ADO/DAO clients for Access are pretty good, so you probably won't run into any concurrency issues.
您使用Excel作为客户端，就像使用WinForms应用程序或其他工具一样。用于访问的ADO/DAO客户机非常好，因此您可能不会遇到任何并发问题。
That being said, Access does NOT scale well. This works great if you have 2 or 3 (or even 10) users. If you are going to have 100, you'll probably run into problems. Also, I tended to find that Access needed regular maintenance in order to not have corruption issues. Regular backups of the Access DB are a must. Compacting the access database on a regular basis will help prevent database corruption, in my experience.
话虽如此，访问的规模并不大。如果您有2个或3个(甚至10个)用户，那么这将非常有用。如果你有100个，你可能会遇到问题。而且，我倾向于发现，访问需要定期维护，以避免出现腐败问题。必须定期备份访问数据库。根据我的经验，定期压缩访问数据库将有助于防止数据库损坏。
Any other things I should be aware of?
我还需要注意什么?
You're doing this the hard way. Using Excel to hit Access is going to be a lot more work than just using Access directly.
你这样做很困难。使用Excel点击访问将比直接使用访问要多得多。
I'd recommend looking into the Access VBA API - most of it is the same as Excel, so you'll have a small learning curve. The parts that are different just make this easier. You'll also have all of the advantages of Access reporting and Forms, which are much more data-oriented than the ones in Excel. The reporting can be great for things like this, and having the Macros and Reports will make life easier in the long run. If the user's going to be using forms to manage everything, doing the forms in Access will be very, very similar to doing them in Excel, and will look nearly identical, but will make everything faster and smoother.
我建议您查看Access VBA API——它的大部分与Excel相同，因此您将得到一个小的学习曲线。不同的部分使这更容易。您还将拥有访问报告和表单的所有优势，这些优势比Excel中的数据更加面向数据。报告对于这样的事情是很好的，拥有宏和报告从长远来看会使事情变得更容易。如果用户要使用表单来管理一切，那么在Access中执行表单将非常非常类似于在Excel中执行这些操作，并且看起来几乎相同，但是会使一切变得更快、更流畅。
#2
15
I do this all the time. If you're using ADO, you're not really using Access, but Jet, the underlying database. That means anybody with Excel can use the app - Access not required. Oh I should mention, the place I work bought a bunch of Office Small Business licenses - no Access. Prior to working here, I would have assumed that anyone who had Excel would also have Access. Not so.
我一直这么做。如果您正在使用ADO，那么您实际上并不是在使用Access，而是使用底层数据库Jet。这意味着任何使用Excel的人都可以使用这个应用程序——不需要访问。哦，我得说，我工作的地方买了一堆办公小型企业许可证——没有权限。在这里工作之前，我假设任何有Excel的人都有访问权限。不是这样的。
I create one class for every table in Access. I very rarely run queries through ADO, instead I keep that logic in the class modules. I read in with a SELECT statement and write out with and UPDATE or INSERT using the Execute method of the ADODB.Connection object.
我为访问的每个表创建一个类。我很少通过ADO运行查询，而是将该逻辑保存在类模块中。我使用ADODB的Execute方法读取SELECT语句，并使用和更新或插入。连接对象。
See http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/
参见http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/
if you want to see how I set up my code.
如果你想知道我是如何设置代码的。
To answer your questions: It will be a small learning curve for you if you already know Excel VBA, but there will be some learning to do; you will pay a performance penalty over doing it all in Access, but it's not that bad and only you can decide if it's worth it; and you can have multiple people accessing the database.
回答你的问题:如果你已经知道Excel VBA，这将是一个小的学习曲线，但是会有一些学习;你会在访问过程中付出性能代价，但这并不是那么糟糕，只有你自己才能决定是否值得;你可以让多人访问数据库。
#3
9
Just skip the excel part - the excel user forms are just a poor man's version of the way more robust Access forms. Also Access VBA is identical to Excel VBA - you just have to learn Access' object model. With a simple application you won't need to write much VBA anyways because in Access you can wire things together quite easily.
只要跳过excel部分——excel用户表单只是一个穷人版的更健壮的访问表单。此外，Access VBA与Excel VBA是相同的——您只需学习Access的对象模型。使用一个简单的应用程序，您不需要编写太多的VBA，因为在访问中，您可以很容易地将事物连接在一起。
#4
8
If the end user has Access, it might be easier to develop the whole thing in Access. Access has some WYSIWYG form design tools built-in.
如果最终用户有访问权限，那么在Access中开发整个东西可能会更容易。Access内置了一些WYSIWYG表单设计工具。
#5
7
Unless there is a strong advantage to running your user form in Excel then I would go with a 100% Access solution that would export the reports and data to Excel on an ad-hoc basis.
除非在Excel中运行用户表单有很大的优势，否则我将采用100%访问解决方案，该方案将报告和数据导出到Excel中。
From what you describe, Access seems the stronger contender as it is built for working with data:
you would have a lot more tools at your disposal to solve any data problems than have to go around the limitations of Excel and shoehorn it into becoming Access...
根据您的描述，Access似乎是一个更强大的竞争者，因为它是为处理数据而构建的:您可以使用更多的工具来解决任何数据问题，而不需要绕过Excel的限制，把它硬塞进Access中……
As for your questions:
至于你的问题:
-
Very easy. There have been some other questions on SO on that subject.
See for instance this one and that one.非常容易。在这个问题上还有其他问题。比如这个和那个。
-
Don't know, but I would guess that there could be a small penalty.
The biggest difficulty I see is trying to get all the functionalities that Access gives you and re-creating some of these in Excel.我不知道，但我猜可能会有一个小处罚。我看到的最大的困难是尝试获得Access提供的所有功能，并在Excel中重新创建其中的一些功能。
-
Yes, you can have multiple Excel users and a single Access database.
Here again, using Access as a front-end and keeping the data in a linked Access database on your network would make more sense and it's easy as pie, there's even a wizard in Access to help you do that: it's just 1 click away.是的，您可以有多个Excel用户和一个访问数据库。同样，使用Access作为前端并将数据保存在网络上的链接访问数据库中会更有意义，而且非常简单，甚至还有一个向导可以帮助您做到这一点:只需单击1下即可。
Really, as most other people have said, take a tiny bit of time to get acquainted with Access, it will save you a lot of time and trouble.
You may know Excel better but if you've gone 80% of the way already if you know VBA and are familiar with the Office object model.
实际上，正如大多数人所说，花一点时间来了解Access，它将为您节省大量的时间和麻烦。您可能更了解Excel，但是如果您已经了解VBA并熟悉Office对象模型，那么您已经了解了80%的内容。
Other advantages of doing it in Access: the Access 2007 runtime is free, meaning that if you were to deploy to app to 1 or 30 PC it would cost you the same: nothing.
You only need one full version of Access for your development work (the Runtime doesn't have the designers).
在Access中做它的其他好处是:Access 2007运行时是免费的，这意味着如果你要将应用程序部署到1或30个人电脑上，那么你的花费是一样的:没有。开发工作只需要一个完整的访问版本(运行时没有设计器)。
#6
3
It really depends on the application. For a normal project, I would recommend using only Access, but sometimes, the needs are specific and an Excel spreadsheet might be more appropriate.
这取决于应用程序。对于一个正常的项目，我建议只使用Access，但是有时候，需要是特定的，Excel电子表格可能更合适。
For instance, in a project I had to develop for a former employer, the need was to give access to different persons on forms(pre-filled with some data, different for each person) and have them complete them, then re-import the data.
例如，在我为以前的雇主开发的一个项目中，需要对表单上的不同人进行访问(预先填写一些数据，每个人的数据不同)，并让他们完成它们，然后重新导入数据。
Since the form was using heavy number crunching, it made more sense to build it in Excel.
由于表单使用了大量的数字运算，所以用Excel构建更有意义。
The Excel workbooks for the different persons were built from a template using VBA, then saved in a proper location, with the access rights on the folder.
针对不同人员的Excel工作簿是从使用VBA的模板构建的，然后保存在适当的位置，并具有文件夹上的访问权限。
All workbooks were attached as External tables to the workbooks, using named ranges. I could then query the workbooks from the Access Application. All administrative stuff was made from the db, but the end users only had access to their respective workbook.
所有工作簿都使用命名范围作为外表附加到工作簿上。然后我可以从访问应用程序查询工作簿。所有的管理内容都是由db来完成的，但是最终用户只能访问他们各自的工作簿。
Developping an Excel/Access application this way was a pleasant experience and the UI was more user-friendly than it would have been using Access.
以这种方式开发Excel/Access应用程序是一种愉快的体验，与使用Access相比，UI更加友好。
I have to say that in this case, it would have taken a lot more time doing it in Access than it took using Excel. Also, the Application Object Model seems better though in Excel than in Access.
我不得不说，在这种情况下，在Access中操作要比使用Excel花费更多的时间。而且，在Excel中应用程序对象模型似乎比在Access中更好。
If you plan to use Excel as a front-end, do not forget to lock all the cells, but the editable ones and don't be affraid to use masked rows and columnns (to construct output tables for the access database, to perform intermediate calculations, etc).
如果您打算使用Excel作为前端，请不要忘记锁住所有的单元格，但是要锁住可编辑的单元格，不要害怕使用掩码行和列(为访问数据库构造输出表，执行中间计算，等等)。
You should also turn off autocalculation while importing data.
您还应该在导入数据时关闭自动计算。
#7
2
It's quite easy and efficient to use Excel as a reporting tool for Access data. A quick "non programming" approach is to set a List or a Pivot Table, linked to your External Data source. But that's out of scope for *.
A programmatic approach can be very simple:
使用Excel作为访问数据的报告工具非常简单和有效。一个快速的“非编程”方法是设置一个列表或一个数据透视表，链接到您的外部数据源。但这超出了*的范围。方案方法可以非常简单:
strProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";"
Set cnn = New ADODB.Connection
cnn.Open strProv
Set rst = New ADODB.Recordset
rst.Open strSql, cnn
myDestRange.CopyFromRecordset rst
That's it !
就是这样!
#8
1
Given the ease of use of Access, I don't see a compelling reason to use Excel at all other than to export data for number crunching. Access is designed to easily build data forms and, in my opinion, will be orders of magnitude easier and less time-consuming than using Excel. A few hours to learn the Access object model will pay for itself many times over in terms of time and effort.
考虑到访问的易用性，我认为除了导出数据进行数字处理之外，使用Excel根本没有什么令人信服的理由。Access的设计目的是容易地构建数据表单，在我看来，它将比使用Excel更简单、更省时。学习访问对象模型的几个小时将在时间和精力上为自己付出很多倍的代价。
#9
1
I did it in one project of mine. I used MDB to store the data about bills and used Excel to render them, giving the user the possibility to adapt it.
我在一个项目中做过。我使用MDB存储账单数据，并使用Excel呈现账单数据，使用户可以对其进行修改。
In this case the best solution is:
在这种情况下，最好的解决办法是:
-
Not to use any ADO/DAO in Excel. I implemented everything as public functions in MDB modules and called them directly from Excel. You can return even complex data objects, like arrays of strings etc by calling MDB functions with necessary arguments. This is similar to client/server architecture of modern web applications: you web application just does the rendering and user interaction, database and middle tier is then on the server side.
不要在Excel中使用任何ADO/DAO。我在MDB模块中实现了所有的公共功能，并直接从Excel中调用它们。通过调用具有必要参数的MDB函数，甚至可以返回复杂的数据对象，如字符串数组等。这类似于现代web应用程序的客户机/服务器架构:您的web应用程序只执行呈现和用户交互，然后数据库和中间层在服务器端。
-
Use Excel forms for user interaction and for data visualisation.
使用Excel表单进行用户交互和数据可视化。
-
I usually have a very last sheet with some names regions for settings: the path to MDB files, some settings (current user, password if needed etc.) -- so you can easily adapt your Excel implementation to different location of you "back-end" data.
我通常有最后一个表，其中包含一些设置的名称区域:MDB文件的路径、一些设置(如果需要的话，是当前用户、密码等)——因此您可以轻松地将您的Excel实现调整为您的“后端”数据的不同位置。
#10
0
To connect Excel to Access using VBA is very useful I use it in my profession everyday. The connection string I use is according to the program found in the link below. The program can be automated to do multiple connections or tasks in on shot but the basic connection code looks the same. Good luck!
使用VBA来连接Excel是非常有用的，我每天都在我的职业中使用它。我使用的连接字符串是根据下面链接中找到的程序。该程序可以自动执行多个连接或任务，但基本的连接代码看起来是一样的。好运！
http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao
#11
0
It Depends how much functionality you are expecting by Excel<->Acess solution. In many cases where you don't have budget to get a complete application solution, these little utilities does work. If the Scope of project is limited then I would go for this solution, because excel does give you flexibility to design spreadsheets as in accordance to your needs and then you may use those predesigned sheets for users to use. Designing a spreadsheet like form in Access is more time consuming and difficult and does requires some ActiveX. It object might not only handling data but presenting in spreadsheet like formates then this solution should works with limited scope.
它取决于您期望Excel<->Acess解决方案提供多少功能。在许多情况下，如果您没有获得完整应用程序解决方案的预算，那么这些小实用程序就可以工作了。如果项目的范围是有限的，那么我将采用这个解决方案，因为excel确实可以根据您的需要灵活地设计电子表格，然后您可以使用那些预先设计好的表单供用户使用。在访问中设计类似表单的电子表格更费时、更困难，而且需要一些ActiveX。该解决方案不仅可以处理数据，而且还可以在表格中显示，这样的解决方案应该适用于有限的范围。
#12
-1
You could try something like XLLoop. This lets you implement excel functions (UDFs) on an external server (server implementations in many different languages are provided).
你可以试试XLLoop。这使您可以在外部服务器上实现excel函数(提供了许多不同语言的服务器实现)。
For example you could use a MySQL database and Apache web server and then write the functions in PHP to serve up the data to your users.
例如，您可以使用MySQL数据库和Apache web服务器，然后使用PHP编写函数向用户提供数据。
BTW, I work on the project so let me know if you have any questions.
顺便说一句，我在做这个项目，如果你有任何问题，请告诉我。