MS Access/ru: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: MS Access/ru

New		Line
1	+	Этот перевод скопирован со страницы User:QLKforges/ru
2	+
3	+	Эта страница объясняет, как использовать базу данных Microsoft Access с использованием [[ODBCConn\|ODBC]] и FPC в SQLdb.
4	+
5	+	## Специфика MS Access
6	+
7	+	### UsePrimaryKeyAsKey
8	+
9	+	В свойствах TSQLQuery, возможно, потребуется установить UsePrimaryKeyAsKey: False
10	+
11	+	### Get just-inserted autonumber primary key
12	+
13	+	After inserting data, Access 2000 and higher supports getting the just-inserted autonumber primary key using this query:
14	+
15	+	``` SQL
16	+	SELECT @@IDENTITY
17	+	```
18	+
19	+	### ODBC Драйвер
20	+
21	+	#### Windows
22	+
23	+	Есть 2 различных [[ODBCConn\|ODBC]] драйвера для Microsoft Access:
24	+
25	+	- Старый драйвер, который можно использовать только формат файла .mdb, называется он "Microsoft Access Driver (*.mdb)". В многих версиях Windows (начиная с Windows 2000, вплоть до Vista); Для более старых версий Windows нужно загрузить 32-х битные компоненты такие, как MDAC или Jet.
26	+	- Новый драйвер, который может получить доступ как .mdb и .accdb файлам, называется "Microsoft Access Driver (.mdb, .accdb)". Доступен как в 32-х битной так, и 64-х битной "Microsoft Access Database Engine 2010 Redistributable";.
27	+
28	+	Как обычно, при использовании 32 бит Lazarus / FPC, использовать 32 водителей немного ODBC. При использовании 64-битной Lazarus / FPC, использовать 64 водителей немного ODBC. Редактируется
29	+
30	+	#### Unix/Linux
31	+
32	+	The [mdbtools project](http://sourceforge.net/projects/mdbtools/) offers limited support for MS Accesss. It includes an ODBC driver which might be used. Try installing packages like mdbtools mdbtools-dev mdbtools-gmdb
33	+
34	+	At least on Debian, the ODBC driver name is "MDBTools".
35	+
36	+	## File-based DSN Instructions
37	+
38	+	A file DSN is simply where the connection settings are written to a file. The reason for having a file DSN is if you want to distribute a data source connection to multiple users on different systems without having to configure a DSN for each system. For instance, I can create a file DSN to a reporting database on my desktop. I can then send the file to my users. My users can save the file DSN to their hard drives and then point their reporting applications at the file DSN.
39	+
40	+	If you want to use a file-based DSN with the SQLDB ODBC driver:
41	+
42	+	### Set up the File DSN
43	+
44	+	- Go to your \[Data sources (ODBC)\] at the control panel administrative tools.
45	+	- Go to \[File DSN\] tab menu, Click Add, then Select \<microsoft access driver\>.
46	+	- Next, browse to your current Lazarus project path to save your .DSN file, because that dsn file will contain the configuration needed to access your database file (.mdb).
47	+	- Click Next, then Finish (you now have created a new .dsn file) of which will be use in your TODBCConnection \[FileDSN\].
48	+
49	+	#### File DSN contents
50	+
51	+	For reference, a file DSN that refers to an MS Access database may look something like this:
52	+
53	+	```DOS
54	+	[ODBC]
55	+	DRIVER=Microsoft Access Driver (*.mdb)
56	+	UID=admin
57	+	UserCommitSync=Yes
58	+	Threads=3
59	+	SafeTransactions=0
60	+	PageTimeout=5
61	+	MaxScanRows=8
62	+	MaxBufferSize=2048
63	+	FIL=MS Access
64	+	DriverId=25
65	+	DefaultDir=
66	+	DBQ=YOUR_msaccess.mdb
67	+	```
68	+
69	+	### Configure your project to use file-based DSN
70	+
71	+	Usage of grids and other data bound controls is the same as for any other SQLDB connector, so that is not covered here.
72	+
73	+	As you're using an ODBC connection, you should have a TODBCConnection object.
74	+
75	+	TOBDCConnection Properties: FileDSN: the path+filename to the DSN file saved earlier, e.g. c:\mylazarus\project1\myFile.dsn Username: admin (or whatever username you need if you are using Access security) this parameter may not be needed if no MS Access security is used Do not put any other entries in the properties.
76	+
77	+	## System/User DSN
78	+
79	+	As explained in the [[ODBCConn\|ODBC]] article, you can also use system or user DSNs, where connection settings are defined in the ODBC control panel instead of stored in a file.
80	+
81	+	The TODBCConnection should have DatabaseName: \<name_of_your_DSN\>
82	+
83	+	## DSN-less connection
84	+
85	+	As explained in the [[ODBCConn\|ODBC]] article, you can also create a DSN-less connection to your Access database where you can specify all connection parameters in code, something like:
86	+
87	+	``` pascal
88	+	//Notice we're using the new MS Access driver, we could probably use the old one too:
89	+	conn.Driver:='Microsoft Access Driver (.mdb, .accdb)';
90	+	conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
91	+	... add whatever parameters you want/need...
92	+	```
93	+
94	+	## Example code
95	+
96	+	### Lazarus: data bound controls on forms
97	+
98	+	Working Source code sample:(Updated2_with_add_delete_update) <http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip>
99	+
100	+	``` pascal
101	+	unit Unit1;
102	+
103	+	{$mode objfpc}{$H+}
104	+
105	+	interface
106	+
107	+	uses
108	+	Classes, SysUtils, odbcconn, sqldb, db, FileUtil, LResources, Forms, Controls,
109	+	Graphics, Dialogs, DBGrids, DbCtrls, StdCtrls, Printers, PrintersDlgs;
110	+
111	+	type
112	+
113	+	{ TForm1 }
114	+
115	+	TForm1 = class(TForm)
116	+	Button1: TButton;
117	+	Button2: TButton;
118	+	Button3: TButton;
119	+	Button4: TButton;
120	+	Datasource1: TDatasource;
121	+	DBEdit1: TDBEdit;
122	+	DBEdit2: TDBEdit;
123	+	DBGrid1: TDBGrid;
124	+	ODBCConnection1: TODBCConnection;
125	+	PrintDialog1: TPrintDialog;
126	+	SQLQuery1: TSQLQuery;
127	+	SQLTransaction1: TSQLTransaction;
128	+	procedure Button1Click(Sender: TObject);
129	+	procedure Button2Click(Sender: TObject);
130	+	procedure Button3Click(Sender: TObject);
131	+	procedure Button4Click(Sender: TObject);
132	+	procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
133	+	procedure FormShow(Sender: TObject);
134	+	private
135	+	{ private declarations }
136	+	procedure PrintDbGrid(dbGrid:TdbGrid);
137	+	public
138	+	{ public declarations }
139	+	end;
140	+
141	+	var
142	+	Form1: TForm1;
143	+
144	+	implementation
145	+
146	+	{ TForm1 }
147	+
148	+	procedure TForm1.FormShow(Sender: TObject);
149	+	begin
150	+	//connection
151	+	ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
152	+	ODBCConnection1.Connected := True;
153	+	ODBCConnection1.KeepConnection := True;
154	+
155	+	//transaction
156	+	SQLTransaction1.DataBase := ODBCConnection1;
157	+	SQLTransaction1.Action := caCommit;
158	+	SQLTransaction1.Active := True; //not applied to ms access(false)
159	+
160	+	SQLQuery1.DataBase := ODBCConnection1;
161	+	SQLQuery1.UsePrimaryKeyAsKey := False;
162	+
163	+	SQLQuery1.SQL.Text := 'select * from table1';
164	+
165	+	// :id is the parameter for the field name id
166	+	SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
167	+	//:name is the parameter for the field name name
168	+	SQLQuery1.updateSQL.Text := 'update table1 set name=:name where id=:id';
169	+
170	+	DataSource1.DataSet := SQLQuery1;
171	+	DBGrid1.DataSource := DataSource1;
172	+	DBGrid1.ReadOnly := true;
173	+
174	+	DBEdit1.DataField := 'id';
175	+	DBEdit1.DataSource := DataSource1;
176	+	DBEdit2.DataField := 'name';
177	+	DBEdit2.DataSource := DataSource1;
178	+
179	+	SQLQuery1.Open;
180	+	end;
181	+
182	+	function MulDiv(nNumber, nNumerator, nDenominator: Integer): Integer;
183	+	begin
184	+	Result:=Round(int64(nNumber)*int64(nNumerator)/nDenominator);
185	+	end;
186	+	procedure TForm1.PrintDbGrid(dbGrid:TdbGrid);
187	+	const
188	+	LeftMargin = 0.05;
189	+	TopMargin = 0.05;
190	+	BottomMargin = 0.05;
191	+	var
192	+	i: integer;
193	+	x,y: integer;
194	+	begin
195	+	if PrintDialog1.Execute then
196	+	begin
197	+	Printer.BeginDoc;
198	+	Printer.Canvas.Font.Size := 12;
199	+
200	+	y := Round(TopMargin*Printer.PageHeight);
201	+	dbGrid.DataSource.DataSet.First;
202	+	while not dbGrid.DataSource.DataSet.Eof do
203	+	begin
204	+	x := Round(LeftMargin*Printer.PageWidth);
205	+	for i := 0 to dbGrid.DataSource.DataSet.FieldCount-1 do
206	+	begin
207	+	printer.Canvas.TextOut(x,y,dbGrid.DataSource.DataSet.Fields[i].AsString);
208	+	x := x + MulDiv(dbGrid.Columns[i].Width,72, dbGrid.Width);
209	+	end;
210	+	dbGrid.DataSource.DataSet.Next;
211	+	y := y + printer.Canvas.TextHeight('A');
212	+	if y > (1-TopMargin-BottomMargin)* Printer.PageHeight then
213	+	begin
214	+	y := Round(TopMargin*Printer.PageHeight);
215	+	Printer.NewPage;
216	+	end;
217	+	end;
218	+	Printer.EndDoc;
219	+	end
220	+	else
221	+	Form1.caption := 'NO PRINTER INSTALLED';
222	+	end;
223	+
224	+	procedure TForm1.Button1Click(Sender: TObject);
225	+	begin
226	+	PrintDbGrid(DBGrid1);
227	+	end;
228	+
229	+	procedure TForm1.Button2Click(Sender: TObject);
230	+	begin
231	+	if button2.Caption = 'new' then
232	+	begin
233	+	SQLQuery1.Insert;
234	+	button2.Caption := 'save';
235	+	exit
236	+	end
237	+	else
238	+	begin
239	+	if (dbedit1.Text = '') or (dbedit2.Text = '')
240	+	then
241	+	begin
242	+	SQLQuery1.Cancel;
243	+	end
244	+	else
245	+	begin
246	+	if SQLQuery1.State = dsInsert then
247	+	begin
248	+	SQLQuery1.Post;
249	+	SQLQuery1.ApplyUpdates;
250	+	Form1.caption := 'ADDED';
251	+	end;
252	+	end;
253	+	end;
254	+	button2.Caption := 'new';
255	+	end;
256	+
257	+	procedure TForm1.Button3Click(Sender: TObject);
258	+	begin
259	+	if SQLQuery1.RecordCount>0 then
260	+	begin
261	+	SQLQuery1.Delete;
262	+	SQLQuery1.ApplyUpdates;
263	+
264	+	//REMOVED or ADD this 2 lines if there is a problem in your SQLdB
265	+	//THESE 2 lines apparently work around a bug in SQLdB for MS_Access dataBase'
266	+	//**** missing reference to bugtracker item; please create a bugtracker item
267	+	//SQLQuery1.Close;
268	+	//SQLQuery1.Open;
269	+
270	+	Form1.caption := 'DELETED';
271	+	end;
272	+	end;
273	+
274	+	procedure TForm1.Button4Click(Sender: TObject);
275	+	begin
276	+	if SQLQuery1.RecordCount>0 then
277	+	begin
278	+	SQLQuery1.Edit;
279	+	SQLQuery1.Post;
280	+	Sqlquery1.ApplyUpdates;
281	+	Form1.caption := 'UPDATED';
282	+	end;
283	+	end;
284	+
285	+	procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: boolean);
286	+	begin
287	+	SQLQuery1.Close;
288	+	CanClose := True;
289	+	end;
290	+
291	+	initialization
292	+	{$I unit1.lrs}
293	+
294	+	end.
295	+	```
296	+
297	+	### Create a database programmatically
298	+
299	+	Using either ODBC driver, you can programmatically create Microsoft Access databases. (Note: error handling has not been tested; please update page if you tested it)
300	+
301	+	Example program:
302	+
303	+	``` pascal
304	+	program CreateAccessDb;
305	+
306	+	{$mode objfpc}{$H+}
307	+
308	+	uses
309	+	{$IFDEF UNIX}{$IFDEF UseCThreads}
310	+	cthreads,
311	+	{$ENDIF}{$ENDIF}
312	+	Classes, sysutils,
313	+	Windows;
314	+
315	+	Const
316	+	ODBC_ADD_DSN=1;
317	+	ODBC_CONFIG_DSN=2;
318	+	ODBC_REMOVE_DSN=3;
319	+	ODBC_ADD_SYS_DSN=4;
320	+	ODBC_CONFIG_SYS_DSN=5;
321	+	ODBC_REMOVE_SYS_DSN=6;
322	+	ODBC_REMOVE_DEFAULT_DSN=7;
323	+	function SQLConfigDataSource(hwndParent: Integer; fRequest: Integer;
324	+	lpszDriverString: PChar; lpszAttributes: PChar): Integer; stdcall; external 'odbccp32.dll';
325	+	function SQLInstallerError(iError: integer; pfErrorCode: PInteger; lpszErrorMsg: string; cbErrorMsgMax: integer; pcbErrorMsg: PInteger): integer; stdcall; external 'odbccp32.dll';
326	+
327	+	function CreateAccessDatabase(DatabaseFile: string): boolean;
328	+	var
329	+	DBPChar: PChar;
330	+	Driver: PChar;
331	+	ErrorCode, ResizeErrorMessage: integer;
332	+	ErrorMessage: PChar;
333	+	retCode: integer;
334	+	begin
335	+	driver := 'Microsoft Access Driver (.mdb, .accdb)';
336	+	{ With this driver,
337	+	CREATE_DB/CREATE_DBV12 will create an .accdb format database;
338	+	CREATE_DBV4 will create an mdb
339	+	http://stackoverflow.com/questions/9205633/how-do-i-specify-the-odbc-access-driver-format-when-creating-the-database
340	+	}
341	+	DBPChar:=PChar('CREATE_DBV4="'+DatabaseFile+'"');
342	+	retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
343	+	if retCode<>0 then
344	+	begin
345	+	//try alternate driver
346	+	Driver := 'Microsoft Access Driver (*.mdb)';
347	+	DBPChar:=PChar('CREATE_DB="'+DatabaseFile+'"');
348	+	retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
349	+	end;
350	+	if retCode=0 then
351	+	begin
352	+	result:=true;
353	+	end
354	+	else
355	+	begin
356	+	result:=false;
357	+	ErrorCode:=0;
358	+	ResizeErrorMessage:=0;
359	+	// todo: verify how the DLL is called - use pointers?; has not been tested.
360	+	GetMem(ErrorMessage,512);
361	+	try
362	+	SQLInstallerError(1, @ErrorCode, ErrorMessage, SizeOf(ErrorMessage), @ResizeErrorMessage);
363	+	finally
364	+	FreeMem(ErrorMessage);
365	+	end;
366	+	raise Exception.CreateFmt('Error creating Access database: %s', [ErrorMessage]);
367	+	end;
368	+	end;
369	+
370	+	begin
371	+	writeln('Result: ');
372	+	write(CreateAccessDatabase('d:\cop\t\bla.mdb'));
373	+	end.
374	+	```
375	+
376	+	## Example database
377	+
378	+	Microsoft Access ships with the Northwind example database.
379	+
380	+	If you only have the Access Runtime installed, you can use this alternative database from the Mondial project as a test database: [1](http://databases.about.com/od/sampleaccessdatabases/a/Microsoft-Access-Sample-Database-Countries-Cities-And-Provinces.htm)
381	+
382	+	## See also
383	+
384	+	- [[ODBCConn]] The ODBC connector this article uses
385	+
386	+	---
387	+	From the Free Pascal wiki, page "MS Access/ru", as it stood on 2020-02-20 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history