Excel to access database

Rupesh Nagarkar
Rupesh Nagarkar used Ask the Experts™
on
Hi,

I am Rupesh & I am new to this forum.

I am trying to build a tool in excel which can update database maintained in access.

There will be 3 to 4 user for this excel tool which will be connected to one access database.

The excel will act as the front end tool & access as a backend tool.

I want excel VBA to perform 3 things:

1. It will update data in access based on 2 column which will act as a unique code.
2. It will add any new row if the data doesnt match with previous unique code.
3. If the user wants to delete perticular row / range then it will be deleted from database also.

Below sample of table:

Country      Product code      Sub-Brand      Mfg Base
Russia      SRU030008869020137      Syrups      Own
Russia      SRU030008869020141      Syrups      Own
Russia      SRU030008564220158      Tablets      Own
Russia      SRU030008564220258      Tablets      Own
Russia      SRU030008564220558      Tablets      Own
USA      SRU030008869020137      Syrups      Own
USA      SRU030008869020141      Syrups      Own
USA      SRU030008564220158      Tablets      Own
USA      SRU030008564220258      Tablets      Own
USA      SRU030008564220558      Tablets      Own

1st two column will act as a unique key.

We cannot use a replacement code since there will be multiple user working on different region, replacement option can change  or erase others database.

Thanks for the help in advance.

Regards,

Rupesh.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Welcome Rupesh.

Using Excel as a front end to Access is not a good idea.  It would be far better to create an Access application to act as the front end.

I don't develop in Excel so I can't tell you how to make forms but you would need to create user forms and since they won't be bound to the Access database, you would need to write all the necessary code to take whatever action you wanted in the Access database.  You could use buttons on the form to run the  "find" or "add" or "delete" code.  The code would be using either DAO or ADO to update data.  You should not need to do any OLE automation.

Author

Commented:
Thank you for the reply.

Can you provude some helps in terms of vba code which i can pluggedin excel.
Distinguished Expert 2017

Commented:
Sorry but I can't.  As I said, I don't develop with Excel.  I use Access.   Even though VBA is VBA, the object models are completely different.  I added Excel Forms to your topics list to draw in some Excel people.

Author

Commented:
Once again thanks for the quick reply & adding this topic to excel forum.
Hope i get some vba code help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial