ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Accecc2000&ado
13 сообщений из 13, страница 1 из 1
Accecc2000&ado
    #32104330
inevity
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
inevity
Гость
Помогите!
База на MS SQL Server 7, использую mdb(а не проект), подключение делаю "вручную" никаких связей эксесовских, встраиваний, присоединений и прочего использовать не хочу(в аксесе неделю). код:

Public ADOConnection As ADODB.Connection
Function OpenADOConnection() As Connection
If ADOConnection Is Nothing Then
Set ADOConnection = New ADODB.Connection
ADOConnection.ConnectionString = "FILE NAME=путь к udl"
ADOConnection.CursorLocation = adUseClient
ADOConnection.Open
End If
Set OpenADOConnection = ADOConnection
End Function


Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select code, flight from way", OpenADOConnection, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rs
field1.ControlSource = rs.Fields(0).Name
End Sub

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

в хелпе ничего не нашел, uniquetable ставил, ключевое поле сделал(code).
Не понимаю в чем собственно проблема.
...
Рейтинг: 0 / 0
11.02.2003, 09:45
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104441
alk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alk
Гость
у меня все работает.
Рекордсет обновляемый?
права на запись есть?
какая ошибка?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Public ADOConnection As adodb.Connection
Function OpenADOConnection() As adodb.Connection
If ADOConnection Is Nothing Then
Set ADOConnection = New adodb.Connection
ADOConnection.ConnectionString =  "Provider=Microsoft.Access.OLEDB.10 . 0 ;Persist Security Info=True;Data Source=DATASERVER\DATASERVER2000;User ID=ZZZ;Password=zzz;Initial Catalog=ts2;Data Provider=SQLOLEDB. 1 "
ADOConnection.CursorLocation = adUseClient
ADOConnection.Open
End If
Set OpenADOConnection = ADOConnection
End Function


Private Sub Form_Load()
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
rs.Open  "select * from tst" , OpenADOConnection, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rs
field1.ControlSource = rs.Fields( 1 ).Name
Me.UniqueTable =  "tst" 
Me.ResyncCommand =  "select * from (tst) where id = ? " 
End Sub
...
Рейтинг: 0 / 0
11.02.2003, 12:15
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104463
-=Alexey=-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-=Alexey=-
Участник
у тебя все работает, потому что у тебя Access 2002 и будет работать даже если ты Me.UniqueTable = "tst" уберешь. Человек же спрашивает по Access 2000 (смотри топик).
...
Рейтинг: 0 / 0
11.02.2003, 12:52
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104478
-=Alexey=-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-=Alexey=-
Участник
в Access2k работает такой код
суть ADOConnection.Provider = "MSDataShape"
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Function OpenADOConnection() As Connection
If ADOConnection Is Nothing Then
  Set ADOConnection = New ADODB.Connection
  ADOConnection.ConnectionString =  "DATA PROVIDER=SQLOLEDB.1 ;INTEGRATED SECURITY=SSPI;INITIAL CATALOG=rate;DATA SOURCE=."
  ADOConnection.Provider =  "MSDataShape" 
  ADOConnection.CursorLocation = adUseClient
  ADOConnection.Open
End If
Set OpenADOConnection = ADOConnection
End Function

Private Sub Form_Open(Cancel As Integer)
Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  rs.Open  "select OrderID, OrderCarN from dataOrder" , OpenADOConnection, adOpenKeyset, adLockOptimistic
  Set Me.Recordset = rs
  Me.txt1.ControlSource = rs.Fields( 1 ).Name
  Me.UniqueTable =  "dataOrder"   'в данном простом случае указывать необязательно'
End Sub
...
Рейтинг: 0 / 0
11.02.2003, 13:04
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104481
-=Alexey=-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-=Alexey=-
Участник
Knowledge Base

ACC2000: Forms Based on ADO Recordsets Are Read-OnlyPSS ID Number: Q227053

Article Last Modified on 05-13-2002

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access 2000
--------------------------------------------------------------------------------
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see Q281998 .

Symptoms
When you try to edit data in a form based on an ActiveX Data Objects (ADO) recordset, the form is read-only. This occurs even if you can successfully edit the recordset directly with ADO.

Cause
Microsoft Access forms will only allow you to edit data from an ADO recordset if the ADO recordset is created by using a combination of the MSDataShape and SQL Server OLEDB providers.

Resolution
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
The resolution depends on the source of data of the recordset that you want to bind to the form's Recordset property.
Data Source Based on Microsoft SQL Server
If the source of the data for the form is provided by Microsoft SQL Server 6.5 or later, you can use the MSDataShape and SQL Server OLEDB providers to create an ADO recordset that can be edited in a Microsoft Access form.

NOTE: Microsoft Access forms will only allow you to edit one table from the recordset on which the form is based. If you set the form's Recordset property to an ADO recordset based on a view, stored procedure, or SQL statement that contains multiple tables, you must set the form's UniqueTable property to the name of the table that you want to edit in the form. Fields from other tables in the form's recordset will be visible on the form, but you will not be able to edit them.

To use the MSDataShape and SQL Server OLEDB providers to create an ADO recordset and to assign the recordset to a form, follow these steps:

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Create an ODBC data source based on the Pubs sample database installed with Microsoft SQL Server.

Open the sample database Northwind.mdb.

Link the Authors table from the data source that you created in step 1.

Create a new form based on the Authors table, and open it in Design view.

Add all fields to the form's detail section.

Clear the form's RecordSource property, so that the form is no longer directly bound to the Authors table.

On the View menu, click Code.

On the Tools menu, click References.

Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.

Click OK to close the References dialog box.

Add the following code to the form's module:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set cn = New ADODB.Connection
   With cn
      .Provider =  "MSDataShape" 
         
      'The code below uses a sample server name, user ID, and password. 
      'Be sure to use your actual server name, user ID, and password.
      .ConnectionString =  "DATA PROVIDER=SQLOLEDB;DATA _
         SOURCE=MySQLServerName;DATABASE=Pubs;UID=sa;PWD=;" 
      .CursorLocation = adUseServer
      .Open
   End With
   
   Set rs = New ADODB.Recordset
   With rs
      .Source =  "SELECT * FROM Authors" 
      .ActiveConnection = cn
      .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
      .Open
   End With
   Set Me.Recordset = rs
   Me.UniqueTable =  "Authors" 
End Sub


Close the form, and save it as frmAuthors.

Open the frmAuthors form in Form view. Note that the form is successfully populated with data from the ADO recordset.

Try to edit any field on the form.

Note that you can successfully edit the form.

Data Source Based on Other Data Sources
If the source of data for the form is provided by any other source, such as the Microsoft Jet database engine, you cannot create an ADO recordset that can be edited with a form, even if you can edit the recordset directly with ADO. The only solution in this case is to use Data Access Objects (DAO) to create the recordset, and then to assign the recordset to the form's Recordset property. DAO is highly optimized for the Microsoft Jet database engine, and can access a number of ISAM or ODBC data sources that are accessible by the Jet database engine.

If you are using an ISAM or ODBC data source, link the table to a Microsoft Jet database (.MDB) and use DAO to open a recordset based on the linked table. As long as the recordset can be edited directly via DAO, a form based on the recordset can be edited as well. To create a DAO recordset that can be edited in a Microsoft Access form, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Open the sample database Northwind.mdb.

Open the Products form in Design view.

Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.

On the View menu, click Code.

On the Tools menu, click References.

Add a reference to the Microsoft DAO 3.6 Object Library if it is not already selected.

Click OK to close the References dialog box.

Add the following code to the form's module:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
Sub Form_Open(Cancel As Integer)
   Dim db As DAO.Database
   Dim rs As DAO.Recordset

   'Assumes the linked table is in the current database
   Set db = CurrentDb
   Set rs = db.OpenRecordset( "SELECT * FROM Products" , dbOpenDynaset)
   Set Me.Recordset = rs      
End Sub


Save and close the Products form.

Open the Products form in Form view. Note that the form is successfully populated with data from the DAO recordset.

Try to edit any field in the form.

Note that you can successfully edit the form.


Status
Microsoft has confirmed this to be a problem in the Microsoft products that are listed at the beginning of this article.


More Information

Steps to Reproduce Behavior
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Open the sample database Northwind.mdb.

Open the Products form in Design view.

Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.

On the View menu, click Code.

On the Tools menu, click References.

Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.

Click OK to close the References dialog box.

Add the following code to the form's module:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   'Use Microsoft Access's OLEDB connection to the Jet database
   Set cn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   With rs
      .Source =  "SELECT * FROM Products" 
      .ActiveConnection = cn
      .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
      .Open
   End With
   Set Me.Recordset = rs      
End Sub


Save and close the Products form.

Open the Products form in Form view.

Note that the form is successfully populated with data from the ADO recordset.

Try to edit any field in the form.

Note that you are unable to edit the form and that Microsoft Access displays the text "Form is read-only" in the status bar.

References
For more information about the form Recordset property, click Microsoft Access Help on the Help menu, type form recordset property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
--------------------------------------------------------------------------------

Send feedback to Microsoft

© 2002 Microsoft Corporation. All rights reserved.
...
Рейтинг: 0 / 0
11.02.2003, 13:11
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104493
alk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alk
Гость
прошу прощенья, действительно не обратил внимание на то что
ACC2000
...
Рейтинг: 0 / 0
11.02.2003, 13:22
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104591
Фотография Ден
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ден
Участник
Проверил примерчик, что -=Alexey=- привел, все замечательно работет под access 2000. А я то наворотил, чтобы это реализовать -))
...
Рейтинг: 0 / 0
11.02.2003, 14:58
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104615
Фотография Ден
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ден
Участник
Хотя, это пример у меня заработал только с реальными таблицами.. С временными он не хочет работать.. А с реальными так работать некрасиво...
...
Рейтинг: 0 / 0
11.02.2003, 15:17
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104710
-=Alexey=-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-=Alexey=-
Участник
напиши код, который работает с временными таблицами и не работает. желательно полностью. будем разбираться :)
...
Рейтинг: 0 / 0
11.02.2003, 16:59
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104851
inevity
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
inevity
Гость
Блин, не работает!
Все приведу, что могу.
CREATE TABLE [dbo].[spr_aircrafts] (
[board] [int] NOT NULL , - тут первичный ключ
[code] [smallint] NOT NULL
) ON [PRIMARY]
GO

Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "MSDataShape"
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PDSP;Data Source=KFM;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=KFM;Use Encryption for Data=False;Tag with column collation when possible=False"
.CursorLocation = adUseClient
.Open
End With


Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM spr_aircrafts"
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs

Me.UniqueTable = "spr_aircrafts"
rs.Fields(0).Value = rs.Fields(0).Value + 10 'Эта и следующая строка работают
rs.Update 'т.е. изменяют данные на сервере

field1.ControlSource = rs.Fields(0).Name
End Sub

Свойства объектов в конце процедуры
RS:
EditMode = adEditNone
CursorType = adOpenStatic
CursorLocation = adUseClientBatch
BOF = False
EOF = False
Index = ""
LockType = adLockOptimistic
RecordCount = 55
State = 1
Status = 0
Sort = ""

ActiveConnection:
CursorLocation = adUseClientBatch
IsolationLevel = adXactReadCommitted
Provider = "SQLOLEDB.1"
State = 1
Version = "2.7"

ME:
Allow(все) = True, кроме
AllowUpdating = 4
DefaultEditing = 1
DefaultView = 0
ConnectControl = <Введенное выражение содержит недопустимую ссылку на свойство "ConnectControl">
DynaSet = <Application-defined or object-defined error>
MaxRecords = 10000
NewRecord = 0
RecordSetType = 4
UniqueTable = "spr_aircrafts"

В меню Tools-References подключены:
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Ole Automation
Microsoft ActiveX Data Objects 2.7

Сервис паков никаких не стоит на офис 2000
...
Рейтинг: 0 / 0
12.02.2003, 03:53
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104896
-=Alexey=-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-=Alexey=-
Участник
вместо ConnectionString = "Provider=SQLOLEDB.1;...
попробуй ConnectionString = "Data Provider=SQLOLEDB.1;...
...
Рейтинг: 0 / 0
12.02.2003, 08:44
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104897
inevity
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
inevity
Гость
Т.к. не понимаю Аксеса, то решил немного потестировать, вот результаты(стоял 97 и 2000 одновременно): убил все аксесы, поставил 2к2. Так вот, в проекте не работает редактирование, а мдб все работает, причем с какими угодно параметрами...:( может из за сервис паков?
...
Рейтинг: 0 / 0
12.02.2003, 08:46
| Ответить | Цитировать | Написать  
Accecc2000&ado
    #32104937
inevity
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
inevity
Гость
Алексей, слово Data не помогло :(
...
Рейтинг: 0 / 0
12.02.2003, 09:58
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Accecc2000&ado
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]