Problem with deleting all records from query table from Excel VBA

[jaryszek]

Ok i understand. But you cant open *.mdb file not having access.
And in my organizations people can't even use other application then Excel, they will say:
"we want training" any my organization will say" sorry, we dont have enough money"...

So Excel is only one solution in my organization Sorry i can do nothing with this.

Code should looks like:

ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [Zgłoszone Wnioski$] IN " & FileName & " WHERE [Login]='ljar02'"

So model is now simple from Administrator view:

1. Administator is changing rows in Excel database (via userform) and each time for edited record and specific user within this record there is an action:
- drop table with specific user name in Access database
- insert to this table all records with this User name - so this is at the same time updating user table so one big query table is also updated.

What do you think ? Maybe this is not a good solution?

Thank you Guys for you support,
and i know that my Organization is strange, sorry.

Jacek Antek

 

[Minty]

You would need a unique record identifier in the Users Excel sheet. I personally would add the UserID, and probably a timestamp. This would give you a unique. Then only import the records that don't already exist into your master table.

 

[static]

Ok i understand. But you cant open *.mdb file not having access.

You can do anything with the data without Access.
Access is not a database engine. It's just a GUI to build databases with.

 

[jaryszek]

static:

In my organization there is only one GUI - Excel
What will you recommend to use as GUI instead Access?

Minty:
what with records which are already exists and they need only update?
I don't see your poiont of view unfortunately.

Unique numbers in Excel - ok. but rest...

Jacek

 

[static]

static:

In my organization there is only one GUI - Excel
What will you recommend to use as GUI instead Access?

Data in Access > user interface in Excel

As I said above.

I used to build applications in nothing more than html and vbscript for people without Access.

 

[jaryszek]

I am to weak to write something in html, vbscript i know only a little.
Anyway, Thank You very much for your help!

Jacek

 

[static]

I wasn't really suggesting that you should try just that it can be done.

You know you can link an Access table to Excel, right?

 

[Minty]

So you know which are the new records, you therefore by definition also know the existing ones, so Update the existing records and then add the new ones.

 

[jaryszek]

static: i know that, thank you for your tip. I would it try if I had more time, unfortunately i have to do futher this project...

Minty: Please give more details about this.

I have existing record in table tb_ljar01.
User ljar01 changed or addedd one record in his table. Now VBA is joining to access and update his table with edited record from Excel yes? And do you want to update only records with changes or new added records? Yes ?

How can i do it?

If you are saying that all tables from Team Leaders should be upload to one big table with unique number it will be interesting idea but how do only update of changing records. For example User 1 changed record (20 row) and in column Login there will be : "ljar01_2016-10-07 14:50". Table was uploaded to database. Next User 1 also has changed row 20 and now there will be "ljar01_2016-10-07 15:50". How table will identify changed record and update it ?

Jacek

 

[Minty]

You would need two identifiers in your Excel table, last_modified timestamp and created timestamp.

The created one is only set at the time of the new record being created, the Last_modified is only changed if a record is updated.
You store both these in your master Access table. When you go to do an update you compare any existing records
E.g. USER_ID and CREATED_TimeStamp already exist in the master table, so it's an existing record, Excel Last_modified <> Master table Last_Modified then it's been changed and needs to be updated in the master table.
If USER_ID and Created Timestamp do NOT exist in master table then import it as it's a new record.

 

[jaryszek]

It is interesting approach. Thank You Misty,
Thank You Guys for your help and support.

I will try with your solutions !

Best Regards,
Jacek Antek