Solved Connection to the back end SQL Express tables (1 Viewer)

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
Hello,
I have migrated the tables of my Access app to SQL Server Express.
If other people want to use these tables, I know I have to install Access or Runtime Acces on every computer.
The question is: how will they connect to the SQL Server tables? Where are exactly those tables nested: in my computer or in the inetrnet?
This is a basic question, but I don't find a basic answer.
many thanks in advance.
Etxe.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:08
Joined
Oct 29, 2018
Messages
21,358
Hi. On which computer did you install SQL Server Express? When you connect your Access database to the new tables, how do you point them to SQL Server Express? You would have to do the same for your users.
 

isladogs

MVP / VIP
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
18,186
There are several things to consider.

First of all, the SQL Server connection needs to be hosted on the network not on your local machine.

Next, what you need to do will depend on the method used for connecting to SQL Server.
If you used the ODBC wizard and a Machine Data Source, that entire process will need to be repeated on each and every machine.
If you used a File Data Source, that may not be necessary provided you used a standard SQL Server driver.

There is a third method called DSN less connections which work in a similar way to the linked table manager.
Its perhaps a bit more effort to setup but it only needs to be done once.
The FE file with the DSN less connection strings will then work successfully on any workstation connected to the network (again provided you used the standard SQL Server driver as this is automatically installed with Access).

If you used a more recent SQL Server driver, it will have some additional features you can use BUT that driver will then need to be installed on each workstation.

For info, all my commercial apps with SQL BEs have DSN less connections for ease of installation by clients and a standard SQL Server driver.
The connection string info is saved in a settings table so the tables can quickly be relinked after any updates are distributed.
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
The SQL Server Express is in my computer: I used
There are several things to consider.

First of all, the SQL Server connection needs to be hosted on the network not on your local machine.

Next, what you need to do will depend on the method used for connecting to SQL Server.
If you used the ODBC wizard and a Machine Data Source, that entire process will need to be repeated on each and every machine.
If you used a File Data Source, that may not be necessary provided you used a standard SQL Server driver.

There is a third method called DSN less connections which work in a similar way to the linked table manager.
Its perhaps a bit more effort to setup but it only needs to be done once.
The FE file with the DSN less connection strings will then work successfully on any workstation connected to the network (again provided you used the standard SQL Server driver as this is automatically installed with Access).

If you used a more recent SQL Server driver, it will have some additional features you can use BUT that driver will then need to be installed on each workstation.

For info, all my commercial apps with SQL BEs have DSN less connections for ease of installation by clients and a standard SQL Server driver.
The connection string info is saved in a settings table so the tables can quickly be relinked after any updates are distributed.
Firstly, thanks for all this information. I am a total beginner, so every answer brings me other doubts and questions.
- the other users will be all over the country: which network do you refer whan ou say "the SQL Server connection needs to be hosted on the network not on your local machine."? We don't have a a professional network, neither do we have a "server" machine, we are a non profit organization, with very limited means. May be you refer to the option you have to pick when you select a destination (see below: I cant pick "Net Framework Data Provider for SqlServer without having such a network can I? ).
What would be the options here? SQL server express+share point for instance?

I have been reading all the Microsoft documenst, but still clueless, so thanks a lot in adavnce for your help!!

1589129946992.png
 

Attachments

  • 1589129660436.png
    1589129660436.png
    404.5 KB · Views: 244

isladogs

MVP / VIP
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
18,186
OK this needs a bit more planning as you're clearly not talking about a local area network (LAN).
In fact it sounds like you don't have wide area network (WAN) either.

How did your users connect to the BE before you converted it to SQL Server?
What problems did you have at that time?

As you are a non profit organisation is there any possible funding for software to manage this scenario?
You may find this thread at another forum useful https://www.accessforums.net/showthread.php?t=80591

In case it helps, see also this MS article https://support.office.com/en-us/ar...b732-77bc6089b84e?ui=en-US&rs=en-US&ad=US#bm2
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
OK this needs a bit more planning as you're clearly not talking about a local area network (LAN).
In fact it sounds like you don't have wide area network (WAN) either.

How did your users connect to the BE before you converted it to SQL Server?
What problems did you have at that time?

As you are a non profit organisation is there any possible funding for software to manage this scenario?
You may find this thread at another forum useful https://www.accessforums.net/showthread.php?t=80591

In case it helps, see also this MS article https://support.office.com/en-us/ar...b732-77bc6089b84e?ui=en-US&rs=en-US&ad=US#bm2

Only one person could use the Access database before, and we want to make it available for new members.
 

isladogs

MVP / VIP
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
18,186
But are you saying each user needs to use the same shared BE?
Is there any money available for managing this?
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175

isladogs

MVP / VIP
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
18,186
One thing I was thinking of was Azure but I have no experience of using it. AWF member @CJ_London may be able to advise
Alternatively consider using something like Terminal Services / Citrix.
All of the above have a cost which needs to be planned for.

Another possible solution is for users to connect remotely using e.g. TeamViewer to a host PC running the FE but only one person can do so at a time.

Whichever you choose, think carefully about where the SQL Server BE is to be stored.
Your workstation isn't going to work as it may be switched off, in use or in hibernation.
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
Hi Isladogs,
I carefully read the whole thread you sent before (citrix-team viewer etc..). There is no way to use terminal services or Teamviewer, as it will be necessary to work in the same time on the app.
Azur looks very promising actually, and as the size of the data is very small, it could be a low cost solution too.
As you suggest to get ahold of CJ London, how should I do please?
Thanks again, very kind of you!
Etxe.
 

isladogs

MVP / VIP
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
18,186
I sent him a PM when I wrote my last reply, Hopefully he will respond in the near future
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:08
Joined
Feb 19, 2013
Messages
16,553
I'm here:geek:

Sql Azure may be an option - costs are based on the plan you buy and are based on data volumes, transaction volumes (number/size of records selected/updated/whatever, number of concurrent users/size of pipeline). Very difficult to get a price, but as a guess for low volume/low userbase perhaps around 70USD/month). maybe more, maybe less. - A poorly designed front end (i.e. forms fetching entire tables rather than just one or two records) will cost more. Consider loading data for lists and combo's once when the app is opened or on first use so that it does not need to be loaded again - for example lists of countries is unlikely to change so make a good candidate, lists of current orders less so - perhaps limit to todays or last 7 days orders.

Benefits are it is very similar to sql server/express you are unlikely to have a problem. Issues will be around performance (poor design again) or an underspec'd pipeline.

Security is generally based around IP addresses, so to be secure you tell azure what IP addresses are allowed access. This means that connecting from anywhere can be a problem - office/home with a fixed IP are OK, but if wanting to access the BE whilst on a train/at a clients/in a coffee house, you will have problems. Having said that I believe it is now possible to login with username and password. What I am not clear about is if that is instead of using a recognised IP or as access to the database part of azure after the IP has been recognised.

There is no way to use terminal services or Teamviewer, as it will be necessary to work in the same time on the app.
I agree using teamviewer only one person at a time, but terminal services does not work that way - each user has their own profile - costs are around 25USD/user/month
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
Hello,
In the case I use Azur, between SqlServer BE stored in Azur or an Azur BE, which one is the most efficient?
For a terminal service, where can I find some documentation please? Will be considered as a "user" every person that will use the Access Front End? (Since I have to consider all the options, I try to figure out how to reduce costs, 300$ a year per user seems quite a lot for our budget)
Thanks for your help.
Etxe.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:08
Joined
Feb 19, 2013
Messages
16,553
In the case I use Azur, between SqlServer BE stored in Azur or an Azur BE, which one is the most efficient?
if you mean store sql server express in Azure and use that rather than sql azure, I've never considered the possibility. But my guess would be like for like, no difference.

For a terminal service, where can I find some documentation please?
google something like 'terminal server online'. Find one that has servers in your country for best performance.

Will be considered as a "user" every person that will use the Access Front End?
yes

300$ a year per user seems quite a lot for our budget
nothing on line (that works) is cheap :)

You can look at installing terminal server on your own equipment - will have lower running costs, but higher capital outlay - plus you have additional maintenance costs in terms of time.

You might also want to investigate using MS Teams. I don't know much about it but you might find terminal server is discounted as part of the package.
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
Hello again,
I just spoke with a Microsoft "specialist": we will have to migrate the SqlServer tabels and queries to SqlAzure, no way to use SqlServer tables "stored" in an "Azure cloud".
As we have a limited amount of data, it is quite cheap to use SQL Azure: not even 10€ per month (back-up included, I don't know how often though): the number of users doesn't impact the price.
Thanks a lot to you and to isladogs, we are not done yet, but you helped us quite a great deal!
Etxe.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:08
Joined
Feb 19, 2013
Messages
16,553
better prices than the last time I looked! good luck with your project.

Make sure you check on how you are going to access the data - if everyone in the office, not a problem, if they need to connect whilst out and about, make sure they can
 

Etxezarreta

Member
Local time
Today, 21:08
Joined
Apr 13, 2020
Messages
175
I will, thanks a lot, and see you around.
If I find anything interesting during the process (technical issues, economic model etc...), I will feed this thread.
Etxe.
 

Users who are viewing this thread

Top Bottom