0

I have an access database split. It is part of an app working along with an excel add-in.

Question:

Within excel, should I address all query updates directly to the back-end version (in the shared network)?

Or should I connect to the front-end (local)?

As I understand splitting access prevents from data corruption in shared database (when users open same file). If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Many thanks in advance.

1

I would connect directly to the Access database back-end part. Why involve the front-end?

...As I understand splitting access prevents from data corruption in shared database (when users open same file). ...

This is not quite correct. Splitting an Access database ...

...Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

Source: Split an Access database (Microsoft Office Website)

...If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Same as above. You are minimizing the risk when splitting an Access database into a front-end (form, queries, etc.) and back-end (data), but you are not eliminating the risk of data corruption.

Consider using a database system like Microsoft SQL Server, Oracle RDBMS, PostgreSQL or MySQL to further reduce the risks of data corruption.

  • Many thanks for your reply! Although I may have not address my question correctly. I know access is not the best tool. I am learning ms SQL server (personal interest) but my dept is excel-based (using access is a milestone!). Based on this I have to get the best out of it. My question is: Should I use VBA within excel to connect to the front-end version of the accs db? or because I am using excel as the front-end, should I just use a conn string directly to the back-end? – Charlie Feb 4 '17 at 11:48
  • Why would you want to connect to the front-end? Connect directly to the back-end. That is where the data is. – John aka hot2use Feb 4 '17 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.