Vba excel adodb connection ошибка
Это мой первый вопрос, так что конструктивная критика приветствуется! Я пытаюсь запросить базу данных доступа из Excel VBA и поместить возвращаемую информацию в диапазон Excel. Я получаю эту ошибку:
Я включил следующие ссылки:
- Visual Basic для приложений,
- Библиотека объектов Microsoft Excel 16.0,
- OLE Automation,
- Библиотека объектов Microsoft Office 16.0,
- Библиотека объектов Microsoft Access 16.0,
- Библиотека Microsoft ActiveX Data Objects 2.0,
Я попытался разместить строку:
Прямо перед строкой ошибки и получил эту ошибку:
Ошибка времени выполнения «3705»: операция не разрешена, когда объект открыт.
Кто-нибудь знает, в чем может быть моя проблема?
2 ответа
Во-первых (и не связано с вашей ошибкой), если вам не требуется поддержка клиентов, использующих Windows 2000 или более раннюю версию, вы должны ссылаться на самую высокую версию объектов данных Microsoft ActiveX вместо 2.0. Если вы используете ADODB только для взаимодействия с базой данных, вам вообще не нужна библиотека объектов Microsoft Access 16.0.
Во-вторых, если у вас уже есть ссылка , не создавайте объекты с поздней привязкой, например:
Раннее добавление ссылки связывает тип, поэтому явно объявите их и создайте их экземпляр, используя New :
Ваша строка подключения должна быть в порядке - там, где вы сталкиваетесь с проблемами, это две строки:
Кроме того, избавьтесь от венгерской записи там - это чертовски запутанно. Команда ADODB не является String , поэтому почему она должна называться strFoo ?
Вы также должны убирать за собой - не оставляйте ваш набор записей и соединение открытым, когда вы закончите с ними. Позвоните .Close , когда закончите.
Наконец, ваш оператор SQL, скорее всего, неверен - вам, вероятно, нужно заключить ваш TID в одинарные кавычки ( ' ):
Это должно выглядеть ближе к этому:
После некоторой тщательной перестановки, я думаю, я понял это. Я удивлен тем, какие изменения устранили проблему, но работает следующий код:
Последняя ошибка была исправлена с помощью:
Эта строка ранее не включала одинарные кавычки вокруг BD0001.
Я также добавил ActiveConnection к объекту Command.
We've got an Exccel spreadsheet used to manage the rota for the out of hours support engineers. A while back, I added a bit of VBA code which automatically diverts the support phone numbers to the engineer's phone out of hours.
It does this by connecting to a database run by the phone providers, and updating the divert number.
This afternoon, it is suddenly not working on the central server:
The code stops at the New ADODB.Connection line, and reports:
I can still run the macro on my laptop, and it works correctly. And on the central server, I can still use Excel, and connect to the datasource correctly. It just won't work through VBA any more on this server.
The macro ran correctly at 9am and diverted all the phones to the office, but the 5pm divert to staff macro didn't work. I can't see that Windows was updated today, or really any other changes at all.
Has anyone seen this problem before?
3 Answers 3
OK found the problem. Looks like at some point, one of the external references for the VBA stuff was unticked (ADO 2.8 Recordset). I have added back the reference and it seems to work OK now.
Since I've somehow got 3 upvotes for answering my own question (!), I'd better put a bit more detail in case other people are seeing this problem:
In the Visual Basic editor, under Tools -> References, I had Microsoft ActiveX Data Objects 2.8 Library selected. But Microsoft ActiveX Data Objects Recordset 2.8 Library was unselected. Interestingly, this library doesn't even appear as an option when looking at it under Windows 7, but the macros work without it.
One more note since evidently a lot of people have this problem. My answer above did solve the problem, but only until certain people edit the file again, at which point, their version of Office automatically re-creates the problem, and I had to solve it again.
There are two longer-term solutions:
2) For my purposes, I moved the macros into another workbook entirely - these macros should only be run from the central server anyway (people just viewing the roster won't have the ODBC data source set up, so the macros won't run anyway). So now the first step the VBA in the macro's workbook does is to open up the actual roster workbook, and it then runs the rest of the VBA code unchanged.
After I got the above working, I copied all the global variables/constants from the routine, which includedinto a similar module in another spreadsheet. I also copied into the same moduleI added the same reference (ADO 2.8)
The first spreadsheet still works; the seccond at DB_Conn.Open pops up "Run-time error '-214767259 (80004005)': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" Removing the references on both, saving files, re-opening, re-adding the references doesn't help. The one still works and the other gets the error.
@Stevoni: No, it was copying a module from one spreadsheet to another. Both spreadsheets had to connect to the same SQL Server instance, so the only thing to change would be the authentication for the particular DB. I am now retired,and can't say how I resolved it, but I do know it eventually worked.
4 Answers 4
I observed the same error message and in my case nothing had changed. I wondered if my odbc driver needed to be reinstalled (based on what i read online). In any case, restarting excel did the trick. Sometimes the solution is much simpler. :-)
When the error pops up, check your "locals" windows to see what the CS holds. View > Locals Window
Problem: Your constant isn't found by the compiler.
Solution: With the constant being located in a separate module, you'll need to set it as Public for the other code to see it.
Proof:
In order to prove this theory you can do the following: Open a new Excel spreadsheet Go to the VBA designer and add a new module In this module put:
Then add the following code to ThisWorkbook:
After adding this return to the workbook and add a button, selecting "TestString" as the macro to run when clicked.
Click the button and a blank message box will appear. Go back to the VBA designer and change the const in Module1 to Public Click the button on the spreadsheet and you should now see "Test String" in the message box.
У нас есть таблица Exccel, используемая для управления расписанием для инженеров поддержки вне часов. Некоторое время назад я добавил немного кода VBA, который автоматически перенаправляет номера телефонов поддержки на телефон инженера в нерабочее время.
он делает это, подключаясь к базе данных, запущенной телефонными провайдерами, и обновляя номер переадресации.
сегодня днем он внезапно не работает на центральном сервере:
код останавливается на New ADODB.Connection линии, и сообщает:
Я все еще могу запустить макрос на своем ноутбуке, и он работает правильно. И на центральном сервере, я все еще могу использовать Excel, и правильно подключаться к источнику данных. Он просто больше не будет работать через VBA на этом сервере.
макрос работал правильно в 9 утра и перенаправил все телефоны в офис, но 5 вечера отвлечения на персонал макрос не работал. Я не вижу, что Windows была обновлена сегодня или действительно какие-либо другие изменения в все.
кто-нибудь видел эту проблему раньше?
OK нашел проблему. Похоже, в какой-то момент одна из внешних ссылок для материала VBA была снята (набор записей ADO 2.8). Я добавил ссылку, и теперь она работает нормально.
Так как у меня как-то есть 3 upvotes для ответа на мой собственный вопрос (!), Я бы лучше поставил немного больше деталей, если другие люди видят эту проблему:
в Редакторе Visual Basic в разделе Инструменты - > ссылки у меня было Microsoft ActiveX Data Objects 2.8 Library выбранные. Но!--1--> был снят. Интересно, что эта библиотека даже не отображается как опция при просмотре ее под Windows 7, но макросы работают без нее.
еще одна заметка, так как, очевидно, у многих людей есть эта проблема. Мой ответ выше решил проблему, но только до тех пор, пока некоторые люди не отредактируют файл снова, и в этот момент их версия Office автоматически воссоздает проблему, и мне пришлось решить ее снова.
есть два долгосрочных решений:
2) для моих целей я полностью переместил макросы в другую книгу - эти макросы должны запускаться только с центрального сервера в любом случае (у людей, просто просматривающих реестр, не будет настроен источник данных ODBC, поэтому макросы все равно не будут запускаться). Итак, теперь первый шаг VBA в книге макроса - открыть фактический реестр книга, а затем выполняется остальная часть кода VBA без изменений.
Я делаю много работы VBA и много сталкивался с этим в последнее время. Я напишу программу и она будет работать хорошо в течение длительного времени (лет в некоторых случаях), а затем в один прекрасный день некоторые компьютеры начинают получать эту ошибку.
одна из наиболее очевидных вещей для разработчика-это ADODB.Подключение и / или ADODB.Recordset перестают самостоятельно капитализировать себя. Либо ADODB строчный, либо вторая часть. Иногда, правда, capiltization нормально и он по-прежнему происходит.
разделение создания объекта connection и / или recordset от изменения кода части "Set new" исправило его каждый раз для меня.
в частности, следующие настройки кода всегда исправляли это для меня:
изменить создание любых объектов подключения из:
аналогично, измените создание любых объектов набора записей из:
У меня была аналогичная проблема, когда мой код VBA отлично работал на моей локальной машине (Windows 7), но запуск его с сервера Citrix (Windows 2003 Server) не сделал и не удалось с ошибкой времени выполнения 430 при попытке установить соединение (Set Conn = New ADODB.Соединение.)
Я не думал о различиях в версиях windows, пока не прочитал эти ответы, и поэтому, когда я unchecked "Microsoft ActiveX Data Objects 2.8 Library" и проверил " Microsoft ActiveX Data Objects 2.7 Library", все работало нормально.
просто хочу это и сказать спасибо за эти посты, которые ведут меня в правильном направлении.
This article introduces how to open ADO connection and Recordset objects.
Original product version: SQL Server
Original KB number: 168336
Summary
ActiveX Data Objects (ADO) offers several ways to open both the Connection and Recordset objects. This article presents sample code for several common techniques for each object.
More information
There are several ways to open a Connection Object within ADO:
- By Setting the ConnectionString property to a valid Connect string and then calling the Open() method. This connection string is provider-dependent.
- By passing a valid Connect string to the first argument of the Open() method.
- By passing a valid Command object into the first argument of a Recordset's Open method.
- By passing the ODBC Data source name and optionally user-id and password to the Connection Object's Open() method.
There are three ways to open a Recordset Object within ADO:
- By opening the Recordset off the Connection.Execute() method.
- By opening the Recordset off the Command.Execute() method.
- By opening the Recordset object without a Connection or Command object, and passing a valid Connect string to the second argument of the Recordset.Open() method.
This code assumes that Nwind.mdb is installed with Visual Basic, and is located in the C:\Program Files\DevStudio\VB directory :
ERROR NOTES
Only the ADO Connection object has an errors collection. The observant reader will notice that a lightweight error handler is in effect for the RecordSet.Open examples. In the event of an error opening a RecordSet object, ADO should return the most explicit error from the OLEDB provider. Some common errors that can be encountered with the preceding code follow.
If you omit (or there is an error in) the DefaultDir parameter in the connect string, you may receive the following error:
If there is an error in the Dbq parameter in the connect string, you may receive the following error:
The preceding errors also populate the Connection errors collection with the following errors:
For each error, the ADO Error number is the same, in this case translating to 0x80004005, which is the generic E_FAIL error message. The underlying Component did not have a specific error number for the condition encountered, but useful information was never-the-less raised to ADO.
Читайте также: