Connect Ms access to SQL Server

[Abdulaym]

Good day?


Please am new in ms Access, I have an inventory I want to share between 4 system. But when I did my research I found out that Ms Access can only accommodate data of 2GB only so therefore during my research I discover that SQL Server can go far beyond 2GB.

My question now is:
1. How can I connect my Ms access to SQL Server
2. If connected to SQL can it be accessible online and offline.
Am new on in Ms Access please I need help. Thanks

[Welshgasman]

Never done it, but I have Google on this phone.
https://support.microsoft.com/en-gb/...9-f3c556f139ea

[Abdulaym]

Ok, thanks
But I won't mind if you can recommend another server that can be of help to me

[madpiet]

you can download the evaluation edition of SQL Server... it all works, but you can only store (I think) 10GB of data. Or did you mean something like MySQL?

[CJ_London]

Ms Access can only accommodate data of 2GB only

that is quite a lot for most applications- a client of mine has around 150000 stock items spread over 20 locations with around 25000 transactions/month. Currently takes up just over 1/2Gb. Current projections indicate it won’t approach 2gb for 7 years or so before there is a need to archive data.

with regards accessible online - depends what that actually means. If it is a closed system where only employees have access then yes, it can be done . If you want all and sundry to access it then no - although you can have a web based sql server and develop user interfaces as web pages - but that would not be using access and development would cost perhaps 10 times as much and take 10 times longer and be more expensive to maintain

so it depends on the scale of your project, the timescale and budget. Quite possible access is not the right development tool for you, it is after all a rapid development tool

[madpiet]

1. use linked tables.
2. "offline" as in not connected to SQL Server? Then how do you access the data? Magic?

You mean between 4 computers on the same network? 4 sites that may be many miles apart? not enough information to answer.

[Abdulaym]

1. use linked tables.
2. "offline" as in not connected to SQL Server? Then how do you access the data? Magic?

You mean between 4 computers on the same network? 4 sites that may be many miles apart? not enough information to answer.

Thanks for your response I really appreciate sir, 4 system on the same network/place.

Sir also is there away the manager can also have access to the database if he's not within the building. i.e. online. please sir how do I go about the 4 system in the same network i.e. offline and that of the one apart i.e. online? Thanks for your usual assistance sir

[Abdulaym]

that is quite a lot for most applications- a client of mine has around 150000 stock items spread over 20 locations with around 25000 transactions/month. Currently takes up just over 1/2Gb. Current projections indicate it won’t approach 2gb for 7 years or so before there is a need to archive data.

with regards accessible online - depends what that actually means. If it is a closed system where only employees have access then yes, it can be done . If you want all and sundry to access it then no - although you can have a web based sql server and develop user interfaces as web pages - but that would not be using access and development would cost perhaps 10 times as much and take 10 times longer and be more expensive to maintain

so it depends on the scale of your project, the timescale and budget. Quite possible access is not the right development tool for you, it is after all a rapid development tool

Okay thanks for your response sir I really appreciate

[Welshgasman]

Thanks for your response I really appreciate sir, 4 system on the same network/place.

Sir also is there away the manager can also have access to the database if he's not within the building. i.e. online. please sir how do I go about the 4 system in the same network i.e. offline and that of the one apart i.e. online? Thanks for your usual assistance sir

The manager could use a remote access program like Anydesk to access his PC from elsewhere. I use that to help my sister 200 miles away, now and again.

[Abdulaym]

Thank you, this is very helpful

[Abdulaym]

https://www.accessforums.net/member.php?u=52269

[CJ_London]

The manager could use a remote access program like Anydesk to access his PC from elsewhere.

That assumes the manager has two computers, one in the office and one for out on the road - although could use a smartphone when on the road if whatever task needs to be done is fairly simple and uses basic forms.

Since this is a limited userbase, could look at using Terminal Server or Citrix or perhaps a VPN can be set up.

This is all speculation as the user detail requirement is vague.

[Abdulaym]

Thanks for your contribution, your contribution will be helpful

[NoellaG]

I manage the SQL server park of a large company (servers are in Germany) from home or everywhere where I can connect to the internet. I connect through VPN.
You can make your database secure using the logins/user/roles security system of Windows combined with that of SQL Server.
If you use reporting services, you can publish your reports on the intranet, so the manager can download his reports on his smartphone.
Compare SQL server editions on https://learn.microsoft.com/en-us/sq...l-server-ver16.

Most front-end apps like Access, linked to your database server, can be made available through CITRIX.