UPDATE table vs INSERT INTO - increasing security

[jaryszek]

Hi Guys,

I am thinking about protecting whole Excel-Access process in my company.

Now consultants have 15 workbooks and after adding new ticket to table adodb connection via VBA is starting:

1. Drop existing table based on its name, for example consultant login: ljar01 will drop table tb_ljar01 from Access Database.
2. Creating new table with headers from Consultant workbook in Access.
3. Inserting whole data from Consultant workbook to Access created table( tb_ljar01).

Problem is sometimes when Consultant accidentally do wrong type of data in his/her workbook. For example he writes Date in TEXT columns instead of TEXT in DATE column.

Adodb Connection and VBA will be work in this case like this:

1. Drop existing table based on its name, for example consultant login: ljar01 will drop table tb_ljar01 from Access Database.
2. Creating new table with headers from Consultant workbook in Access.
3. Here error will be showed.

So in Access table there will empty table tb_ljar01 and my query to gather all consultants' tickets in one big table will not be refreshed.

And my data will be not present....

Have you People maybe any ideas to secure this process?

I think about:
1. Replace Insert Into with command Update. So searching through consultant table in Access and change all existing tickets with new ones (update them) and add not yet existing tickets to this table.
But is it possible within Access or SQL?

2. Protect consultants' workbooks in order to secure data and avoid manually done errors by consutatants within theirs workbooks.

What do you think ?
Maybe you have better ideas to secure workbooks?
Maybe there is a better solution to check if inserting data will be not causing any damages?

Please help,
Warm regards,
Jacek Antek

 

[plog]

I really question the purpose of Access in this system. There's 2 big things you mention that are incorrect for using a database:

1. Tables named after users. Tables should be generically named, the data they hold should allow you to partition them. So instead of tbl_ljar01 that table should instead be named something like ConsultantsData and within it should be a field called [consultant] and it should hold the value "ljar01" to denote that record belongs to ljar01.

2. Dynamically created tables. When you set up a database you configure it to hold data, you don't use it like a blank spreadsheet and allow users to add/edit field names. This allows you to control the datatype of data going into it, and allows you validate said data.


It seems you are using Access simply as a backup tool for your spreadsheets. Why? Why not created a directory structure and simply move copies of those spreadsheets into there. Access seems both to be overkill and underkill for this. A directory is much better at assigning and allowing permissions. And will backup the data exactly as needed.

 

[jaryszek]

Hi plog, thank you for your support!

Add. 1
I know where your thoughts are going here. But I have 15 consultants and each of them have his own workbook. How do it in different way ? All of these consultants work on theirs tickets with theirs logins. So they have to unfortunately theirs own tables...

Add. 2
In excel my code VBA is looking like:

Dim HurtowniaADO As New ADODB.Connection
Dim ConnectionString As String
ConnectionString = ark_Listy.Range("ConnStr")
Dim ZdanieSQL As String
Dim Login As String
Login = ark_Listy.Range("UserLog")
Dim FileName As String
FileName = "'" & ark_Listy.Range("FileName")
Dim Zdanie As String

On Error Resume Next

HurtowniaADO.Open ConnectionString
ZdanieSQL = "DROP TABLE [tb_" & Login & "]"
HurtowniaADO.Execute ZdanieSQL

On Error GoTo Koniec

Zdanie = "CREATE TABLE [tb_" & Login & "]([Numer zgłoszenia] TEXT, [Data zgłoszenia] DATE, [Godzina wpływu zgłoszenia] TEXT, [Temat zgłoszenia] TEXT, [Rodzaj zaświadczenia] TEXT, "
Zdanie = Zdanie & "[Sposób odbioru] TEXT, [Rodzaj umowy] TEXT, [Imię i Nazwisko zgłaszającego] TEXT, [PESEL] TEXT, [Numer osobowy] TEXT, [Spółka] TEXT, [System] TEXT, "
Zdanie = Zdanie & "[Szczegóły zgłoszenia] TEXT, [Preferowany sposób kontaktu] TEXT, [Dane kontaktowe] TEXT, [Uwagi zgłaszającego] TEXT, [Konsultant przyjmujący zgłoszenie] TEXT, "
Zdanie = Zdanie & "[Status zgłoszenia (Konsultant)] TEXT, [Zespół odpowiedzialny za realizację] TEXT,[Czas reakcji] TEXT, [Status II LW] TEXT, [Odpowiedź II LW] TEXT, "
Zdanie = Zdanie & "[Załącznik] TEXT, [Osoba realizująca II LW] TEXT, [Data odpowiedzi] DATE, [Godzina odpowiedzi] TEXT, [Koordynator/Osoba zgłaszająca] TEXT, [PESEL_k] TEXT, "
Zdanie = Zdanie & "[Spółka_k] TEXT, [Oddział_k/Stanowisko] TEXT, [Saldo] TEXT, [Kwota] TEXT, [Wizerunek] TEXT, [Skala błędu] TEXT, [Waga błędu] TEXT, [Obszar] TEXT, [Zasadność reklamacji] TEXT, "
Zdanie = Zdanie & "[Czy istnieje konieczność uruchomienia procesów naprawczych] TEXT, [Koszty] TEXT, [Zasadność reklamacji_agent] TEXT, [Rodzaj Reklamacji] TEXT, [Data wypłaty] DATE)"

ZdanieSQL = Zdanie
HurtowniaADO.Execute ZdanieSQL

ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [rejestracja zgłoszeń$] IN " & FileName & ""
HurtowniaADO.Execute ZdanieSQL

HurtowniaADO.Close
Set HurtowniaADO = Nothing

You have to first create table and set up your columns data formats. And your raw data from excel next is exported to Access using Adodb connection.

plog, What do you mean by " a directory structure" ?
It is very interesting and maybe my model can be rebuild?

Thank you for your answers!
Jacek

 

[Minty]

Add. 1
I know where your thoughts are going here. But I have 15 consultants and each of them have his own workbook. How do it in different way ? All of these consultants work on theirs tickets with theirs logins. So they have to unfortunately theirs own tables...

I'll step in here to reinforce what Plog has said - each consultant doesn't need their own workbook / table. They can log in to their local front end of the database and then only see their tickets by adding the consultant ID to the ticketing system.

 

[jaryszek]

Hi Minty,

Problem is that in my company there is no Access. So consultants can work only on Excels' workbooks...

Olny one person has access to the Access - it is me.
So front-end will be good if in my company would be the Access for each user.

Jacek

 

[Minty]

You can use Access run time - it's free. And stops end users being able to change objects.
https://www.microsoft.com/en-gb/download/details.aspx?id=39358

 

[jaryszek]

I know this but there is less functions then casual Access.

How should it look like in Access?

One big table and adding rows by each consultant?
Create one common front-end form in order to fullfilling it?

Jacek

 

[Minty]

It has no less functions, just the end user can't adjust the design of tables / forms etc. It is ideal for distributing an end user front end.

I don't know your data structure, but one big table is not normally the way to go.
Perhaps you could post up some sample data from the current system?

 

[jaryszek]

Could you please confirm if all users can use one Access DataBase in real time and within the same time with this Access Runtime?
What about security? What if access would have "error" and arease my database?
I can't allow that.

I can't post my sample data because of private data security, but i can post sample consultant workbook with headers.
Consultant is fullfilling user form for each ticket.
And after all all results are gathering into one big table in Access.

Jacek

 

[plog]

I haven't heard what you are doing with it other than using it as a sort of backup system to the main system which is Excel. From what we have heard of your tables, you really don't have a relational database, simply a bunch of tables that mirror spreadsheets.

I question the use of Access at all. What purpose is it serving in this system?

 

[jaryszek]

This system is for registering all people phones for polish corporation.
There is a lot of complaints, problems and different topics connected with wages, holidays etc.

So each phone should be register into consultant base (Excel) and gather in one big table in Access.

Jacek

 

[plog]

Still not seeing Access's role in this. Still seems like Access is simply a master spreadsheet--which would be better executed in Excel.

I suggest reading up on what a relational database is and then reading up on normalization.

 

[The_Doc_Man]

Problem is sometimes when Consultant accidentally do wrong type of data in his/her workbook. For example he writes Date in TEXT columns instead of TEXT in DATE column.

Protect consultants' workbooks in order to secure data and avoid manually done errors by consutatants within theirs workbooks.

There is a lot of complaints, problems and different topics connected with wages, holidays etc.

As long as you have non-uniform data gathering you will have this problem. As a person with a software systems engineering background, I can tell you that your problem is NOT on your end, the accumulation and gathering side. It is on the original data input side of the problem. This is worse than the "square peg in a round hole" problem. This is 15 different geometric shapes in a round hole. They just won't fit.

If you cannot impose uniformity of input, you have only a limited chance of achieving ANY goal. The idea of using an Access Runtime solution for a front-end interface that talks to a shared back-end data file would be a start, since you can build forms that do data analysis and can do filtering, fast code-number lookups (via Combo Box), and other useful things to assure the uniformity that you need, and that could still make this tool attractive to those who now use Excel.

The other problem you mentioned is that they might accidentally delete something. But if you use an Access Runtime system with an .MDE or .ACCDE front-end interface file (as appropriate to the version of Access in question), they don't directly interact with the file system. Access does, and you just don't program it to delete anything.

Now, I'll be honest. Imposing uniformity where there was none is a DAUNTING task. Your management has to agree to the project AND when you get it ready for actual use, your management has to agree to enforce its usage. If management isn't on board with the project and its goals, you might as well not start.

About six or seven years ago, my department was in a similar fix with 30 system admins turning in mostly (but not completely) similar spreadsheets to my boss, who needed to roll up the results so HE could turn in reports to HIS boss. He was so totally swamped by this process that he had no time for being a manager. He had become a glorified clerk, which was NOT what he had been hired to do. He was totally frustrated.

He supported my project to do the equivalent of what you are trying to do - gather data in a uniform manner from different sources whose prior input was NOT uniform - and also wasn't necessarily what he really needed to know. I did a requirements analysis and then implemented a solution for gathering what I had discovered was actually needed for his reports. It was a never-ending process of development and product improvement until the day I retired and handed it off to my replacements. But it was satisfying - and it had the desired effect. Suddenly, the boss had time to do the things he was supposed to do, like actually MANAGE his department.

Having been exactly where you are now, I appreciate and understand the situation. What I am telling you is the straight answer of how to get from where you are to where you might wish to be. But in the real world of business, someone has to set - and ENFORCE - priorities on how to get there from your current environment. That isn't something we can help you do.

 

[jaryszek]

Access has query to union all small tables for one big. And after that we can do reporting part.

We have about 350 000 thousand records and this number is too large for Excel (too slow) and still is growing up.
So Access has to be in my company.

I write here to get help because I did not found any solution in internet so please help if you want.

Jacek

 

[The_Doc_Man]

Access has query to union all small tables for one big. And after that we can do reporting part.

In line with my previous comments, until you get your inputs uniformly defined, a UNION query isn't going to be a robust and reliable answer. The basis of a UNION query is to present differently formatted and organized sources into a single source. But if the sources themselves are not internally uniformly formatted and organized, UNION queries will be no better than what you have now, which is a jumbled and disorganized heap of data. If you can't control the nature of the inputs you will never be able to make proper outputs.

 

[Minty]

You have answered your own question really. And The Doc has backed up your answer.

You need to create a system to capture the data from your consultants in a completely uniform manner. You cannot do this robustly in Excel. You can with a database.

Read up on split databases, with each user having their own locked down local front end database (the user interface) linked to a central secure data store on the network (The Back end).

 

[jaryszek]

The Doc Man thank you for your long and comprehensive answer.

So at all you recommend to use Access Runtime in my situation?

Jacek

 

[jaryszek]

Thank you Guys for your help !

In Excel there is fixed data, but if you are saying that this data should be inputed in uniform way - i know that you are suggesting that Access is safer and input data can be fixed and can not be changed.

I am trying to avoid it in Excel - Consultants have only userform and format data is set up by me - for example second columns is DATE. Data base in second sheet will be locked fo editing so input data will be fine.

But i will try to set up one big data base in Access conected to each consultants directly,

Thank you
Jacek

 

[The_Doc_Man]

In the long run, Access forms can do more for you more easily than trying to manage forms in Excel. Since VBA works in Excel, you COULD do what was needed - but in my not-always-so-humble opinion, it is just easier in Access.

Just remember, if your management doesn't buy in to the project of setting up this kind of enforced uniformity, you will be left dangling. Further, your management needs to understand that the safeguards needed to produce the desired results will take a non-trivial amount of time to implement in code. However, if you can get that buy-in, this IS something that can be done.

 

[jaryszek]

The_Doc_Man thank you.

I am sure that my management won't buy anything There is a few thing that i have to learn in order to use Access Forms. Security is in the first place.

I have already read about splitting database in Access. It is simple. But with protection it seems not that easy...

It is possible to lock whole table and enable it only when you are using UserForm?

Jacek