Every day uptading table as flat Excel file into Access

[jaryszek]

Hi Guys,



this is crossed post from here (there is no answer from days):

https://www.access-programmers.co.uk...d.php?t=294220

I would like to add here my sample database where I am linking Dictionary table from Excel (flat file which is automatically updated with QV every day) with Access Front-end.


I created relationships with unique field (Zleceniodawca_nr) between Access table and Excel table like here in the picture:

https://ibb.co/b6eLhk



It is good approach to do that?
Or better :
1) make apend query and update query while access is starting and extract this data into Access table?
2) Do not create relationships between tables and leave Excel file as alone stand by dictionary- table?


Please help, maybe here someone will be known that
Best wishes,
Thank you in advance,
Jacek Antek

[isladogs]

You did get an answer to the original PST at AWF on the same day you posted
Use an append query then an update query

You also said in the OP that the excel file didn't have an ID field.
So import it to a temp 'buffer' table then append from that.
Afterwards delete the temp table

[jaryszek]

Thank you ridders52!

Ok so it is better approach to transform it to Access format , and later add primary key into this table then use Excel file?
And run this queries each time front - end is started?

edit: I am asking about this because of knowledge. Knowledge of doing applications in Access in best possible way.

Thank you,
Jacek

[jaryszek]

Ok i see the problem:

where 2 users are trying to add something using Excel tables it is an error: file is only to read because another user is sitting there....

So obviously I have to create these 2 queries in order to make Access possible to run smoothly...

Best regards,
Jacek

[jaryszek]

My solution to others:

My code:

Public Sub Import()

Dim sDBFile As String
Dim sXLSFile As String
Dim sSheet As String
Dim sTableName As String


DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tbl_temp_dok"


'On Error Resume Next
'    DoCmd.SetWarnings False
'    DoCmd.RunSQL "DROP TABLE tbl_slow_Dokumenty"
'    DoCmd.SetWarnings True
'On Error GoTo 0


sDBFile = "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Aplikacja_Braki_FE.accde"
sXLSFile = "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Słownik.xlsx"
sSheet = "Dokumenty"
sTableName = "tbl_temp_dok"
'
'CurrentDb.Execute "SELECT * INTO [;DATABASE=" & _
'sDBFile & ";]." & sTableName & _
'" FROM [Excel 8.0;HDR=Yes;DATABASE=" & _
'sXLSFile & ";].[" & sSheet & "$];", dbFailOnError
''DoCmd.TransferDatabase acLink, "Microsoft Access", sDBFile, _
acTable, sTableName, sTableName


CurrentDb.Execute "INSERT INTO tbl_temp_dok SELECT * FROM [Excel 8.0;HDR=Yes;DATABASE=" & _
sXLSFile & ";].[" & sSheet & "$];", dbFailOnError


DoCmd.OpenQuery "QryUpdateDok"
DoCmd.OpenQuery "QryAppendDok"
DoCmd.OpenQuery "QryDeleteDok"


DoCmd.SetWarnings True


End Sub

This code is running when user is opening front-end (Access Runtime):
1) delete data from tbl_temp_dok (which is not linked table)
2) Next step is to insert all data drom Excel to Access temp table
3) run update and appned quries

QryUpdateDok - first I am uptading existing fields:

UPDATE tbl_slow_Dokumenty INNER JOIN tbl_temp_dok ON tbl_slow_Dokumenty.Numer=[tbl_temp_dok].Numer 
SET tbl_slow_Dokumenty.Obowiązkowy = [tbl_temp_dok].Obowiązkowy, tbl_slow_Dokumenty.Dokument = [tbl_temp_dok].Dokument;

QryAppendDok - secondly I am appending non existing fields:

INSERT INTO tbl_slow_Dokumenty
SELECT tbl_temp_dok.*
FROM tbl_temp_dok LEFT JOIN tbl_slow_Dokumenty ON tbl_slow_Dokumenty.Numer=[tbl_temp_dok].Numer
WHERE tbl_slow_Dokumenty.Numer Is Null;

edit:

I added also delete query to check if records in tbl_slow_Dokumenty were deleted from tbl_temp:

DELETE tbl_slow_Dokumenty.*FROM tbl_slow_Dokumenty
WHERE tbl_slow_Dokumenty.Numer NOT IN (SELECT tbl_temp_dok.Numer FROM tbl_temp_dok);

I do not know this is good approach to build databases using flat Excel files but referencing to Access Gurus answers - this is a good way.

Best Wishes
Jacek

[jaryszek]

Hello Guys,

i would like to close the topic but I am wondering about one more thing.

What if do you have for example 15 every day updating tables?
For each of them I have to run update,append and delete query and run a code?

Hmm maybe automatization in VBA only?

What do you think Gurus?

Best Wishes,
Jacek