How to connect users to from Ms Access to SQL Server Express

[nector]

Introduction

Pardon me people on this one my full training will be paid in January 2023 for Ms. Access / SQL Server. I have finally installed the SQL server after following the procedures below:

(1) Marching my ODBC 32 BIT to Ms. Access 32 BIT and compose a string with ODBC [SQL Server Client 11.0] version
(2) In order to allow sharing the ODBC string I have use file dsn less ODBC string, my understanding here is that this string will always move with the front end wherever it goes.
(3) All the four workstations I made sure that the ODBC [SQL Server Client 11.0] version 32 BIT is available to avoid mismatch
(4) I have removed the Ms. Access linked tables strings and replaced them with ODBC [SQL Server Client 11.0] version 32 BIT linked table and ensured that the data is available both in Ms. Access linked tables with a world map and SQL database. In short, If I capture any data in Ms. Access, I will find it in SQL server as well.
(5) I have made sure that all open recordset VBA Code have [dbSeeChanges], for example ((Set Rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset,dbSeeChanges))
(6) Under SQL configuration manager I have set static port to 1433 and forwarded the same in windows security firewall when making the inbound rules

Problems Areas

(1) Except my machine is able to use the SQL server because it is installed on it, but the workstations if even they are able to access my machine using WIFI they cannot link to the SQL server.
(2) If I revert back to linked access tables on my machine and share the folder where the backend is, then all machines are able to use my database without problems except there a slit slowness due to the internet as you all know that Ms. Access is not meant to work over the internet, hence the change we want to make to use the SQL server database.

Question

How do I make sure that the users also connect to my database, please note this is just a mare laptop which I have installed the SQL server, there is nothing like ACTIVE DIRECTORY AND DOMAIN to assign group policies and then use the group to access the SQL. That does not exist because to start with I do not have the windows server operating system.

Any suggestion????

 

[GPGeorge]

Introduction

Pardon me people on this one my full training will be paid in January 2023 for Ms. Access / SQL Server. I have finally installed the SQL server after following the procedures below:

(1) Marching my ODBC 32 BIT to Ms. Access 32 BIT and compose a string with ODBC [SQL Server Client 11.0] version
(2) In order to allow sharing the ODBC string I have use file dsn less ODBC string, my understanding here is that this string will always move with the front end wherever it goes.
(3) All the four workstations I made sure that the ODBC [SQL Server Client 11.0] version 32 BIT is available to avoid mismatch
(4) I have removed the Ms. Access linked tables strings and replaced them with ODBC [SQL Server Client 11.0] version 32 BIT linked table and ensured that the data is available both in Ms. Access linked tables with a world map and SQL database. In short, If I capture any data in Ms. Access, I will find it in SQL server as well.
(5) I have made sure that all open recordset VBA Code have [dbSeeChanges], for example ((Set Rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset,dbSeeChanges))
(6) Under SQL configuration manager I have set static port to 1433 and forwarded the same in windows security firewall when making the inbound rules

Problems Areas

(1) Except my machine is able to use the SQL server because it is installed on it, but the workstations if even they are able to access my machine using WIFI they cannot link to the SQL server.
(2) If I revert back to linked access tables on my machine and share the folder where the backend is, then all machines are able to use my database without problems except there a slit slowness due to the internet as you all know that Ms. Access is not meant to work over the internet, hence the change we want to make to use the SQL server database.

Question

How do I make sure that the users also connect to my database, please note this is just a mare laptop which I have installed the SQL server, there is nothing like ACTIVE DIRECTORY AND DOMAIN to assign group policies and then use the group to access the SQL. That does not exist because to start with I do not have the windows server operating system.

Any suggestion????

There are a number of things that need to be done.

First, you must set the SQL Server Express instance to allow remote connections. This usually is the default, but do verify it.

Use SQL Server Configuration Manager, which should have been installed on your computer along with SSE to ensure it's set up properly. Look for documentation on how to do this.

Second, make sure you allow connections through the firewall on your computer for the SQL Server.

Third, SSE is always installed as a named instance. Your users must provide that name exactly as it is to be able to connect. It should be something like: YOURCOMPUTERNAME\NAMEYOUGAVETOYOURSSEINTANCE

There are lots of resources available on the internet for this task, so a broad search is always a good choice.

 

[The_Doc_Man]

WiFi connections are never good. Access REALLY wants to operate over LAN hard-wired. ANY attempt to connect via Wide-Area networks is highly suspicious. Part of THAT problem is "hops." I cannot advise you on using SQL server since I have not worked that, but I would suspect part of the problem is the CONNECT string you are using. I must defer to others who have far more experience with SQL Server on that fine point.

 

[CJ_London]

hence the change we want to make to use the SQL server database.

If you are changing to sql server for performance reasons don’t be surprised if this doesn’t work. Performance is about a) quality of connection (which you are not changing) and b) efficient design of tables, indexing and front end queries to minimise network traffic - which you may or may not of done.

 

[nector]

The whole thing will change because this has to work on internet, and I can see the need for active directory to assign users as a group not individually. However, many thanks to all the contributors to my query.

 

[Pat Hartman]

Access is optimized to work with SQL Server et al only on a LAN. LAN speeds are blinding when compared with WAN speeds. You will be veeeeeeeeeeery disappointed with the speed of this app when connected via WAN. There are better methods.
1. Use Citrix
2. Use RD
3. Use a VPN (but only if the BE is SQL Server - and that means that you may have to make modifications to the app if you have used old style Access techniques like Seek and Find and form filters.)
4. Some people have reported adequate results using Azure. I have not but that doesn't make them wrong. I was working with a cheap third-party hosting service.

 

[nector]

Thank you on this one, we moved to cloud, however we one issue of forms data entry slowness.

 

[CJ_London]

one issue of forms data entry slowness.

I presume you have moved to sql server, not much can be said without knowing what 'moved to the cloud' actually means other than I'm not surprised - per post #4, do you have a fast internet connection? and are your forms optimised for use across the web. See this link

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

However from a performance perspective over the web, Citrix or Remote Desktop can't really be beaten