Использование Excel в качестве интерфейса для доступа к базе данных Access (с VBA)

Я создаю небольшое приложение для друга, и они хотели бы использовать Excel в качестве внешнего интерфейса. (пользовательский интерфейс будет в основном пользовательскими формами в Excel). У них есть куча данных в Excel, которые они хотели бы запросить, но я не хочу использовать excel в качестве базы данных, поскольку я не думаю, что она подходит для этой цели, и я рассматриваю использование Access. [BTW, я знаю, что у Access есть свои недостатки, но есть нулевой бюджет и доступ уже на другом ПК]

Подводя итог, я рассматриваю возможность сбрасывать кучу данных в Access и затем использовать Excel в качестве интерфейса для запроса базы данных и отображения результатов в среде стиля пользовательской формы.

Вопросы:

Насколько легко подключиться к Access из Excel с помощью ADO/DAO? Является ли он ограниченным с точки зрения функциональности или я могу стать творческим?
Я могу заплатить штраф за производительность (vs. использование форм в Access как пользовательский интерфейс)?
Предполагая, что база данных всегда будет обновляться с использованием команд ADO/DAO из Excel VBA, означает ли это, что я могу иметь несколько пользователей Excel, использующих одну единственную базу данных Access, и не запускать какие-либо проблемы concurrency и т.д.? li >
Любые другие вещи, о которых я должен знать?

У меня есть сильные навыки Excel VBA и думаю, что я могу быстро преодолеть Access VBA, но никогда не делал ссылку Excel/Access раньше. Я мог бы обучать данные в Excel и использовать в качестве квази-базы данных, но это просто больнее, чем это необходимо (а не надежное долгосрочное решение).

Любые советы приветствуются.

Алекс

Ответ 1

Я уверен, что вы получите тонну ответов "не делай этого", и я должен сказать, что есть веская причина. Это не идеальное решение....

Сказав это, я пошел по этой дороге (и тому подобным) раньше, главным образом потому, что задание указывало на это как на жесткое требование, и я не мог говорить об этом.

Вот несколько вещей, которые следует учитывать при этом:

Насколько легко установить ссылку на Access из Excel с помощью ADO/DAO? Является ли это довольно ограниченным с точки зрения функциональности или я могу стать творческим?

Это довольно просто. Вы более ограничены, чем если бы делали что-то с помощью других инструментов, поскольку формы VBA и Excel немного более ограничены, чем большинство полных языков программирования, но нет ничего, что будет показательным стопором. Он работает - иногда он немного уродлив, но он работает. В моей последней компании мне часто приходилось это делать - и изредка вытаскивали данные из Access и Oracle через VBA в Excel.

Я могу заплатить штраф за производительность (vs. использование форм в Access как пользовательский интерфейс)?

Мой опыт в том, что определенно есть перфекционист. штраф в этом. Я никогда не заботился (в моем случае, дела были достаточно маленькими, чтобы это было разумно), но переход Excel ↔ Access намного медленнее, чем просто работа в Access. Часть этого зависит от того, что вы хотите сделать.

В моем случае то, что казалось абсолютным самым медленным (и самым болезненным), пыталось заполнить электронные таблицы Excel на основе данных Access. Это было не весело, и часто было очень медленно. Если вам нужно спуститься по этой дороге, обязательно сделайте все с помощью скрытого/невидимого Excel, или перерисовка абсолютно вас убьет.

Предполагая, что база данных всегда будет обновляться с использованием команд ADO/DAO из Excel VBA, означает ли это, что я могу иметь несколько пользователей Excel, использующих одну единственную базу данных Access, и не запускать какие-либо проблемы с concurrency и т.д.? p >

Вы в значительной степени используете Excel в качестве клиента - так же, как вы бы использовали приложение WinForms или любой другой инструмент. Клиенты ADO/DAO для Access довольно хороши, поэтому вы, вероятно, не столкнетесь с проблемами concurrency.

Таким образом, Access не масштабируется хорошо. Это отлично работает, если у вас есть 2 или 3 (или даже 10) пользователей. Если у вас будет 100, вы, вероятно, столкнетесь с проблемами. Кроме того, я, как правило, обнаружил, что Access нуждается в регулярном обслуживании, чтобы не иметь проблем с коррупцией. Регулярное резервное копирование БД доступа является обязательным. По моему опыту, уплотнение базы данных доступа на регулярной основе поможет предотвратить повреждение базы данных.

Любые другие вещи, о которых я должен знать?

Ты делаешь это с трудом. Использование Excel для доступа к Access будет намного больше, чем просто использовать Access напрямую.

Я бы порекомендовал посмотреть в API доступа VBA - большая часть из них такая же, как у Excel, поэтому у вас будет небольшая кривая обучения. Части, которые отличаются друг от друга, делают это проще. У вас также будут все преимущества отчетов Access и Forms, которые намного ориентированы на данные, чем те, которые есть в Excel. Отчетность может быть отличной для таких вещей, и наличие макросов и отчетов облегчит жизнь в долгосрочной перспективе. Если пользователь будет использовать формы для управления всем, делать формы в Access будет очень, очень похоже на их выполнение в Excel, и будет выглядеть почти идентично, но сделает все быстрее и плавнее.

Ответ 2

Я делаю это все время. Если вы используете ADO, вы не используете Access, а Jet - базовую базу данных. Это означает, что любой пользователь Excel может использовать приложение - Access не требуется. О, я должен упомянуть, место, где я работал, купило кучу лицензий Office Small Business - без доступа. До работы здесь я бы предположил, что любой, у кого есть Excel, также будет доступ. Не так.

Я создаю один класс для каждой таблицы в Access. Я очень редко запускаю запросы через ADO, вместо этого я сохраняю эту логику в модулях классов. Я читаю инструкцию SELECT и записываю с помощью и UPDATE или INSERT, используя метод Execute объекта ADODB.Connection.

См. http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/

если вы хотите увидеть, как я настроил свой код.

Чтобы ответить на ваши вопросы: это будет небольшая кривая обучения для вас, если вы уже знаете Excel VBA, но будет некоторое обучение; вы заплатите штраф за выполнение за все это в Access, но это не так уж плохо, и только вы можете решить, стоит ли это; и вы можете иметь несколько человек, обращающихся к базе данных.

Ответ 3

Просто пропустите часть excel. Превосходные пользовательские формы - это всего лишь версия для бедных людей, способ более надежных форм доступа. Также доступ VBA идентичен Excel VBA - вам просто нужно изучить объектную модель Access. С помощью простого приложения вам не нужно писать много VBA в любом случае, потому что в Access вы можете легко соединять вещи.

Ответ 4

Если у конечного пользователя есть Access, может быть проще развить все это в Access. Доступ имеет встроенные средства разработки форм WYSIWYG.

Ответ 5

Если у вас есть сильное преимущество для запуска вашей пользовательской формы в Excel, я бы пошел на 100% -ное решение Access, которое будет экспортировать отчеты и данные в Excel на разовой основе.

Из того, что вы описываете, Access кажется более сильным соперником, поскольку он создан для работы с данными:
у вас было бы гораздо больше инструментов в вашем распоряжении для решения любых проблем с данными, чем для того, чтобы обойти ограничения Excel и обучать его превращению в Access...

Что касается ваших вопросов:

Очень легко. На этот вопрос были еще какие-то вопросы. См. Например этот и тот.
Не знаю, но я бы предположил, что может быть небольшой штраф.
Самая большая трудность, которую я вижу, заключается в попытке получить все функциональные возможности, предоставляемые Access, и повторное создание некоторых из них в Excel.
Да, вы можете иметь несколько пользователей Excel и одну базу данных Access.
И здесь, используя Access как интерфейс и сохраняя данные в связанной базе данных Access в вашей сети, было бы более разумно и просто, как пирог, там даже мастер доступа Access мог бы сделать это: всего лишь 1 клик.

Действительно, как и большинство других людей, нужно немного времени, чтобы познакомиться с Access, это сэкономит вам много времени и проблем.
Вы можете знать Excel лучше, но если вы уже прошли 80%, если знаете VBA и знакомы с объектной моделью Office.

Другие преимущества этого в Access: Время выполнения Access 2007 бесплатное, что означает, что если вы должны развернуть приложение до 1 или 30 ПК стоило бы того же: ничего.
Вам нужна только одна полная версия Access для вашей разработки (у Runtime нет дизайнеров).

Ответ 6

Это действительно зависит от приложения. Для обычного проекта я бы рекомендовал использовать только Access, но иногда потребности специфичны, и таблица Excel может быть более подходящей.

Например, в проекте, который я должен был разработать для бывшего работодателя, необходимо было предоставить доступ к различным лицам по формам (предварительно заполненные некоторыми данными, разными для каждого человека) и их завершать, затем повторно - импортировать данные.

Поскольку форма использовала хруст тяжелого числа, было больше смысла строить его в Excel.

Учебники Excel для разных лиц были построены из шаблона с использованием VBA, а затем сохранены в соответствующем месте с правами доступа к папке.

Все книги были прикреплены в качестве внешних таблиц к рабочим книгам с использованием названных диапазонов. Затем я мог запросить книги из приложения Access. Все административные материалы были сделаны из db, но у конечных пользователей был доступ к их соответствующей книге.

Разработка приложения Excel/Access таким образом была приятным опытом, и пользовательский интерфейс был более удобным для пользователя, чем при использовании Access.

Я должен сказать, что в этом случае в Access было бы гораздо больше времени, чем при использовании Excel. Кроме того, модель объекта приложения выглядит лучше, чем в Excel, чем в Access.

Если вы планируете использовать Excel в качестве front-end, не забудьте заблокировать все ячейки, но редактируемые и не бойтесь использовать маскированные строки и столбцы (для построения таблиц вывода для базы данных доступа, для выполнения промежуточных вычислений и т.д.).

Вы также должны отключить автокалькуляцию при импорте данных.

Ответ 7

Очень просто и эффективно использовать Excel в качестве средства создания отчетов для данных Access. Быстрый "не-программный" подход заключается в установке списка или сводной таблицы, связанной с вашим внешним источником данных. Но это не доступно для Stackoverflow.
Программный подход может быть очень простым:

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

Что это!

Ответ 8

Учитывая простоту использования Access, я не вижу убедительной причины использовать Excel вообще, кроме как экспортировать данные для хруста. Доступ разработан, чтобы легко создавать формы данных и, на мой взгляд, будет на порядок проще и менее трудоемко, чем использование Excel. Несколько часов, чтобы изучить объектную модель Access, будут оплачивать себя много раз с точки зрения времени и усилий.

Ответ 9

Я сделал это в одном проекте. Я использовал MDB для хранения данных о счетах и использовал Excel для их рендеринга, предоставляя пользователю возможность его адаптировать.

В этом случае лучшим решением является:

Не использовать ADO/DAO в Excel. Я реализовал все как публичные функции в модулях MDB и вызвал их непосредственно из Excel. Вы можете возвращать даже сложные объекты данных, например массивы строк и т.д., Вызывая функции MDB с необходимыми аргументами. Это похоже на клиент-серверную архитектуру современных веб-приложений: ваше веб-приложение просто выполняет взаимодействие с визуализацией и пользователем, базу данных и средний уровень на стороне сервера.
Используйте формы Excel для взаимодействия с пользователем и для визуализации данных.
У меня обычно есть последний лист с некоторыми областями имен для настроек: путь к файлам MDB, некоторые настройки (текущий пользователь, пароль, если необходимо и т.д.) - чтобы вы могли легко адаптировать вашу реализацию Excel к разным расположение данных "back-end".

Ответ 10

Чтобы подключить Excel к Access с помощью VBA, очень полезно использовать его в своей профессии каждый день. Строка подключения, которую я использую, соответствует программе, найденной в приведенной ниже ссылке. Программа может быть автоматизирована для выполнения нескольких подключений или задач при съемке, но основной код подключения выглядит одинаково. Удачи!

http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao

Ответ 11

Это зависит от того, какую функциональность вы ожидаете от решения Excel ↔ Acess. Во многих случаях, когда у вас нет бюджета для получения полного решения приложения, эти небольшие утилиты действительно работают. Если объем проекта ограничен, тогда я бы пошел на это решение, потому что excel дает вам гибкость при разработке электронных таблиц в соответствии с вашими потребностями, а затем вы можете использовать эти предопределенные листы для пользователей. Разработка электронной таблицы, такой как форма в Access, требует больше времени и сложностей и требует некоторого ActiveX. Этот объект может не только обрабатывать данные, но и представлять их в виде таблиц, например formates, тогда это решение должно работать с ограниченной областью.

Ответ 12

Вы можете попробовать что-то вроде XLLoop. Это позволяет вам выполнять функции excel (UDF) на внешнем сервере (предусмотрены серверные реализации на разных языках).

Например, вы можете использовать базу данных MySQL и веб-сервер Apache, а затем написать функции в PHP, чтобы обслуживать данные для ваших пользователей.

Кстати, я работаю над проектом, поэтому дайте мне знать, если у вас есть какие-либо вопросы.