Ok:
I have solution for this!!!
First of all user is fullfiling In Excel's Useform his database within his workbook with proper data.
Excel added this data after user clicks Send Button to sheet "Reported tickets" (attachment User's database) There is empty column - FormattedTicket Number (5000020_2016_4)
After send button also in sheet "Counter of Entries" is added to table:
TicketNo - 5000020
Time Entry - 2016-03-12 15:34:34
User - login.
Code:
Sub SQL_Baza()
Dim Connectstr As String
Dim HurtowniaADO As New ADODB.Connection
Dim ZdanieSQL As String
Dim Login As String
Dim FileName As String
Dim Moja As New MyForm
Dim Lokalizacja_Pliku As String
Dim Lokalizacja_Folderu As String
Dim TimeEntry As String
Dim TicketNumber As String
Dim Wiersz As String
Dim rs As ADODB.Recordset
Dim NumerSpółki, User, CzasWpisu As String
Dim rsQuery As ADODB.Recordset
// path of the database
Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\Baza_Pełnomocnictwa.mdb"
Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa"
//name of Excel
FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"
//entries in user Workbook
Login = "ljar01"
TicketNumber = "5000020"
TimeEntry = "29-08-2016 14:33:33"
//connection string to access
Connectstr = "DSN=MS Access Database;DBQ=" & Lokalizacja_Pliku & ";DefaultDir=" & Lokalizacja_Folderu & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
On Error Resume Next
// opening databse
HurtowniaADO.Open Connectstr
Set rs = New ADODB.Recordset
Set rsQuery = New ADODB.Recordset
// opening table with all records and adding new entry
rs.Open "tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
![NumerSpółki] = TicketNumber
![CzasWpisu] = TimeEntry
![User] = Login
.Update
End With
// refreshing query with D count Function
rsQuery.Open "qry_tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable
rsQuery.Update
Set rs = Nothing
Set rsQuery = Nothing
And after that my code above in Excel VBA is going:
1. added last record for table with TicketNo, TimeEntry and User on basis of
entry by User (only time is counted by formula Now())
2. Connecting to access and add last row to table1 - with all entries from all users.
3. refreshing query with Dcount function (qryTable)
4. Added first_row from qryTable to sheet "Reported tickets" and that is all.
We have full database.
Database in attachment.
The topic was solved in other forum - if you are interested, here is the link:
http://www.access-programmers.co.uk/...d.php?t=288927