MyTetra Share
Делитесь знаниями!
Подключение из Excel к Access через VBA
Время создания: 10.10.2019 07:05
Текстовые метки: Connect Excel To Access, ADO, Connection, Recordset, Excel-Access, CursorLocation, RecordCount
Раздел: !Закладки - VBA - Access - ADO

Подключение из Excel к Access через VBA

Nov. 23rd, 2016 at 11:14 AM

Полезная функция по подключению из Excel к Access (предварительно надо подключить библиотеку MS ActiveX Data Objects 2.8 Library, как показано на картинке). Проверено - работает.

VBA Добавление Reference

Public Sub test_db()

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ActiveWorkbook.Path & "\Database4.accdb; Jet OLEDB:Database;"

Dim con As New ADODB.Connection

con.Open ConnectionString

On Error GoTo not_table

con.Execute ("SELECT TOP 1 * FROM Customers")


Exit Sub



End Sub

Вам нужно использовать статический курсор. Для этого вам нужно явно создать объект RecordSet, вот так:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.Open "SELECT * FROM " & viewName & ";", objConn, adOpenStatic,adLockReadOnly, adCmdText

Неявно созданные RecordSets имеют серверные курсоры, что приводит к возвращению значения -1.

Для получения дополнительной информации:

NOTE: click here to see superior alternatives to .RecordCount.

RecordCount returns -1
The use of the ADO Recordset's .RecordCount property requires either the use of:

  • Static or Keyset server-side cursors or
  • A client-side cursor (which returns a Static cursor)

(Note: some OLEDB Providers will return the correct recordcount with an adOpenDynamic cursor, others will not).

By default Recordsets are opened server-side, and with an adOpenForwardOnly cursor. Attempting to access the .RecordCount property with this type of cursor will return -1.

The easiest way to fix this is to change the cursor type to adOpenStatic. Doing this requires you to explicitly create a recordset object:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

Attempting to implicitly create a recordset, eg like this:

Set objRS = objConn.execute(strSQL)

will not work, as the implicitly created recordset will have a default adOpenForwardOnly cursor.

As mentioned above, the alternative method is to use a client-side cursor. The client referred in this case is the OLEDB Cursor Service.

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objConn,,adLockReadOnly, adCmdText

In order to be able to use server-side cursors and .Recordcount, the Recordset object must support either Approximate Positioning or Bookmarking. There has been discussion on the ActiveServerPages list to the effect that the MS Oracle OLEDB Provider (or earlier versions of this provider) do not support either Approximate Positioning or Bookmarking, hence require client-side cursors in order for .RecordCount to work.

The use of ADO constants requires you to define them. You can get information on doing this here. For more information on the Recordset's .Open method click here.

Back to FAQ Listing

Так же в этом разделе:
MyTetra Share v.0.53
Яндекс индекс цитирования