Access does not load in 1s. Source code of the function to create a database file

The function receives 3 parameters:

1. FileName - string name of the database file.
2. DataTable - a table of values ​​containing data.
3. Table Description - a structure describing the table fields and name.

To work with the database, a connection that is already familiar to us is created. Having connected, we create an object responsible for executing sql instructions. Next, we organize a loop through the rows of the table of values ​​(function parameter No. 2) and a nested loop through the columns of the table. The row counter will be used to index rows. The column counter will be used to search for a field description in the table description structure (parameter No. 3). Going through the columns of the next row, we analyze their types (according to the table description) and perform the appropriate transformations. As a result, we have a ready-made sql command for writing a line to the database. We apply the received sql instruction using the ADODB.Command object. At the end of the process, close the connections and clear the memory.

Unfortunately, on this site I was not able to attach processing for the thick client, which can upload any directories to the access database. But you can download it on Yandex disk: http://yadi.sk/d/UzK_PAsJ3vjTS. This processing involves all the parts of the universal unloading mechanism. Of course, this mechanism is not limited to this processing. It can be safely copied as in separate module, and in your processing. I do not rule out erroneous situations that could lead to upload failure, but this is not a commercial project. You are free to modify this mechanism to suit your needs. Below are 2 appendices describing adox constants. I hope this mechanism will be useful to you. I wish you success in your professional career!

Appendix 1 list of additional properties of the access table field:

AutoIncrement

Sets the field type to "Counter" with the value automatically incremented when a record is added.

Default

Default field value.

Description

Description of the field.

Fixed Length

Determines whether the field will be fixed length or variable.

Increment

The value by which the counter type field will be incremented.

Nullable

Determines whether the field can contain no value, in other words, whether the field should be required.

Seed

The value from which counting will begin in the first record for a field of type "counter".

Jet OLEDB:Allow Zero Length

Determines whether text fields can contain zero-length strings. Ignored for non-character fields.

Jet OLEDB:AutoGenerate

Determines whether a new GUID value will be automatically generated for fields of type adGUID.

Jet OLEDB:Column Validation Rule

An expression that determines the correctness of the value written in the field must be written in SQL WHERE format but without keyword WHERE.

Jet OLEDB:Column Validation Text

The text that will be displayed if the value entered in the field does not correspond to the rule defined for this field (Rule).

Jet OLEDB:Compressed UNICODE Strings

Determines whether Microsoft Jet will compress UNICODE strings when writing to disk. Ignored if the database is not in Microsoft format Jet version 4.0.

Jet OLEDB:Hyperlink

Specifies that the data in the field is a hyperlink. Ignored for fields with a type other than adLongVarWChar.

Jet OLEDB:IISAM Not Last Column

For Installable-ISAMs, this property informs the I-ISAM that there are more columns that are going to be added to the table after this one. If you are using ITableDefinition::AddColumn or ITableDefinition::CreateTable, it is required that you set this property for every.

Jet OLEDB:One BLOB per Page

Determines whether data should be stored in separate pages (True) or can use shared database pages to save disk space. Works only with adLongVarBinary fields.

Appendix 2 list of value types used in ADOX

adDouble, type 5 - fractional number

adDate, type 7 - date/time

adCurrency, type 6 - integer

adInteger, type 3 - unsigned integer

adBoolean, type 11 - boolean

adVarWChar, type 202 - unicode string 255 characters long

adLongVarWChar("memo"), type 203 - unicode string unlimited string

Recently I needed to download all the data from one simple Access database into a self-written configuration (1C:Enterprise. There wasn’t much information to transfer and, in principle, it was possible to offload this fascinating process to the user, but I decided to help my already busy employees.

The help consisted of complete automation of this process. I had to write a simple processing that did everything in a few seconds. Before this task, I didn’t have to work with the 1C + Access combination, so before I grabbed the keyboard and started spanking the code, I decided to read the manuals.

I knew from the very beginning that all interaction would involve using the appropriate providers, but I still decided to dig into the examples.

I thought for a long time in what vein to write this note. In the end, I settled on the simplest option - provide the code for my solution. Even if it is not very optimal, it is more than enough for most tasks.

So, let's start looking at everything in order. We create a new processing and throw a single component on it - “TableField”. I stretched this component across the entire form. As a result, my form looked like in Figure 1.

Figure 1. Processing form

At this point we can assume that the form design is ready. We will not create columns manually, but will perform this procedure dynamically. Fortunately, it's not difficult. Create a “On Click” event handler for the “Run” button and write the following code in it:

//Clear our table field TableField1.Clear(); TableField1.Columns.Clear(); //Preparing the connection string for MS Access //The path to the database is indicated at the very end Connection string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+"C:\mydb.mdb"; ConnectionWithBase = New COMObject("ADODB.Connection"); //Connect ConnectionWithBase.Open(ConnectionString); //Get a set of records from the table demo_tableRecords Collection = New COMObject("ADODB.Recordset"); RecordsCollection.Open("select * from demo_table", DatabaseConnection, 1); //read the structure of the table fields //In the table of values, create all the columns that are in the Access table For count = 0 by Records Collection.Fields.Count - 1 Cycle TableField1.Columns.Add("Column" + count, Row(Records Collection .Fields.Item (count).Name)); Add(); For count = 0 by RecordsCollection.Fields.Count - 1 Cycle NewRow["Column" + count] = RecordsCollection.fields(TableField1.Columns[count].Value; EndCycle.MoveNext(); End of the Cycle; //Display data from the specification in the TableFieldFormElements.TableField1.Value = TableFormElements.TableField1.CreateColumns();

The code turned out to be not too big, but quite useful. I did not make any bindings to a specific structure. The code works great on a wide variety of tables. I have nothing more to add, so I'll say goodbye

In short - no way. In everything related to interaction with government agencies (be it tax, financial statements, all sorts of excise taxes, etc.), 1C cannot be replaced by anything and trying to do this is, to say the least, unconstructive.

But don’t stop reading this article, this is not the main idea yet. The main thing is not to fall into a logical trap and consider 1C completely indispensable in any part of the automation of all processes in the enterprise. Here there is something to argue with and something to offer. I want to talk about one successful experience in automating various aspects of the business activities of a computer company and, of course, about connecting this solution with 1C. The work grew out of the need to interface a self-developed system with the 1C program, but this approach (division into 2 program blocks) can be developed into a full-fledged philosophy for building an information system in general.

Initially the task was as follows. There was corporate system automation written in Microsoft Access. Over the many years of its development, it has absorbed almost all everyday tasks - maintaining a bank, issuing invoices, preparing accounting documents for shipment, maintaining a warehouse, accounting for components serial numbers, compilation technological maps for production, etc. Recently, the following additions have been added to the system’s capabilities: creation of bank payments directly based on incoming invoices, automatic generation price lists in various formats for publication on web resources, updating the company’s website and preparing Contracts based on existing templates (so-called office automation). Plus the presence of a large amount of analytics and maintaining all management accounting. All this made the system completely irreplaceable - transferring all this functionality to 1C would have taken a lot of time and money.

On the other hand, the 1C-Enterprise program served only the needs of the accounting department for maintaining accounting and accounting records and submitting reports. Thus, 1C was “untied” from the daily functioning of the company, and the completed documents were entered into the database a posteriori.

What is good about this scheme? There are several advantages:
- exclusion of managers and other (ir)responsible persons from the “holy of holies” - the accounting database, which should be formed only by employees who have an understanding of the subject; accurate maintenance of directories,
- removal from 1C of everything that is not related to it: from management tasks that do not require reflection in accounting, to all other points that are specific to the company and to which outsiders should not have access,
- savings on licenses: 1C workstations cost significant money,
- important point: when implementing all the additional functionality described above directly “inside” 1C, there is a constant need for its improvement when product updates are released. This, of course, guarantees almost constant employment of 1C programmers, but this is a place where you can save significantly.

Well, the big minus, of course, is the need to double check all the documents and update the directories.

Actually, this was the main task - to get away from double work.

First you need to deal with all sorts of directories - first of all, this is a directory of contractors and items. The problem of directory synchronization, which so often arises in programs of this kind, has been sufficiently worked out and is understandable in implementation. However, in this case, a strong-willed decision was made not to use 2 directories with mutual synchronization, but to have one initial version - the one entered in MS Access, and simply replicate it in 1C ( feedback- this is just a mark in MS Access that this position has already been unloaded).
This approach greatly simplifies the task. For purely accounting needs, you need very few positions in such directories - and they are also included in external program so as not to violate the formulated principle. This way you can remove one more common problem- immense bloat of the reference book of used nomenclature. Below is the product group “Monitors”

Loading such horror into the 1C directory (and new monitor models appear almost every week) can be configured in such a way that they all fall into one position, since the nuances of the color of the frame around the screen and the number of pixels are completely insignificant for accounting! And, to be honest, the size of the diagonal is even more so if it differs by half an inch.

Further, in an external program, you can work out the merging of duplicate positions in directories, especially in the directory of counterparties, with much less losses, since in 1C this takes a lot of effort.

Thus, only “refined”, cleaned and brought into proper form data is uploaded to 1C. Unloading can be done with some frequency - portioning allows you to debug the work of the accounting department and avoid unnecessary work if during the day (or a longer period) something was canceled, changed, carried out again - all these things happen in the working MS Access database, and only “established” "The state of affairs is reported to 1C. Plus, this kind of unloading can be synchronized with backup operations and you can always be sure that the current state of the 1C database is correct.

It’s clear that documents arrive without automatic posting (although this could be configured). By the way, the MS Access database itself checks the availability of the necessary goods in the warehouse - for writing off for shipment (the dilemma is to make sure that the invoice can be issued current date that all the items necessary for write-off are in the warehouse - that is, purchased or produced - you need... to post the document in 1C. Apparently, you shouldn’t trust this to managers - or an accounting employee should always stand behind the manager)

Now about the implementation. 1C programs support a data exchange interface through the ADO mechanism. On the 1C side, the standard one is used software interface to create elements of directories and documents, so that the operation of this mechanism does not depend on the internal details of the 1C implementation, which may change from version to version and from update to update. On the other hand, the main development slogan was “to make maximum use of 1C capabilities where possible.” Therefore, in the final implementation of the bundle, only the name and TIN of the counterparty were loaded from MS Access - the data was “pulled up” from the Unified State Register of Legal Entities and Individual Entrepreneurs using the mechanism built into 1C (the same applies to bank details– they are updated using the BIC).

Queries were created in an external MS Access database that prepare all the necessary data for loading into 1C - take it and arrange it into documents and fields.

The unloading scheme consists of two stages. First, new positions in the directories of counterparties and items are loaded. The GUIDs assigned to 1C are returned to MS Access so that when uploading all documents at the second stage, the correct references to directories can be set. It looks like this

We believe that such an ideology of construction information systems can be taken as a basis not only in those companies where there are legacy non-1C systems, but also when organizing the work of new legal entities, since we have a lot of experience with MS Access. The advantages of this approach are described above.

In this article we will talk about setting up user access rights to 1C system objects.

In 1C 8, to control user access, a separate metadata object is used, which is called Roles.

Pay attention! This article was written to help programmers. Setting up rights in user mode using the example of 1C Accounting is discussed in.

A role defines the set of rights a user has. The role mechanism is very similar to the Windows rights mechanisms Active Directory. For each of the objects (directories, documents), the developer sets his own set of rights - read / write / add / change / ...

Set of available rights - the collection of all permissions in user roles.

If we open the Role metadata object, we can see the following picture:

The object has two tabs - Rights and Restriction Templates. Rights - main tab, Templates - tab for setting rights at the record level in 1C ( RLS). This is a very important topic, which I will try to describe in future articles.

We will only consider the tab Rights.

• Objects— a list for which rights will be set.
• Rights— a list of possible rights settings for setting.
• Restricting access to data— role fields for customization

Please pay attention to the checkboxes at the bottom:

• Set permissions for new objects— if the flag is set for the role, permissive rights will be automatically set on new metadata objects. I recommend installing it if you often forget to set permissions for new objects.
• Set rights for details and tabular parts default— a flag, when set, details and tabular parts will inherit the rights of the owner (directory, document, etc.)
• Independent rights of subordinate objects— if the flag is set, then the system will take into account the rights to the parent object when determining the rights to a configuration object

Permission settings for the entire configuration

If we open the Role and click on the configuration root, we will see the following settings:

More details about each rights to the entire configuration:

Get 267 video lessons on 1C for free:

• Administration— administration information base(requires the “Data Administration” right)
• Data administration— the right to administrative actions over data
• Updating the Database Configuration- the right to
• Monopoly mode— use of exclusive mode
• Active users— view the list of active users
• — log book
• - launch right thin client
• - right to launch web client
• Fat client— right to the role of launching the thick client
• Outer join— the right to start an external connection
• Automation— the right to use automation
• All functions mode— in managed application mode
• Saving user data— permission or prohibition to save user data (settings, favorites, history). This is especially true for 1C managed forms.
• Interactive discovery external treatments — opening external processing
• Interactive opening of external reports— opening external reports
• Conclusion— printing, recording and copying to the clipboard

Setting up 1C 8.2 rights to other metadata objects

For other main objects (directories, constants, documents, registers...), the set of rights for the role is quite standard:

• Reading- reading (software)
• Addition- adding (software)
• Change- change (software)
• Removal- removal (software)
• View— view
• Interactive addition- interactive addition
• Editing— editing
• Interactive deletion flag— interactive mark for deletion
• Interactively unmark deletion— unmark for deletion
• Interactive removal of marked— deleting marked objects
• Line input— using line input mode
• Interactive removal— direct deletion (shift +del)

Rights only for documents:

• Interactive conducting— carrying out
• Cancellation— cancellation of documents
• Interactive conducting non-operative— holding (with standard form commands) a document in non-operational mode
• Interactive cancellation— interactive cancellation
• Interactive change of spent— editing the posted document. If the role's right is not set, the user cannot delete a posted document, set a deletion mark, resend it, or make it unposted. The form of such a document opens in viewing mode

P.S. If you still can’t figure out the user roles, you can order .
Video with an example of setting up rights in 1C Accounting 3.0: