Teething issues with File DSN ODBC (Ms Access/SQL Server)
Kindly correct me if I’m wrong on the Less DSN File:
(1) Once the ODBC string is done and tested from the developmental computer, that ODBC less DSN File will move with the application where it goes, the only thing to be done is install the correct ODBC version on each work station AND install the database on central place while the work station will either have full Ms Access or Runtime + ODBC.
Questions
(1) What about those who are using VPN can the same ODBC mentioned above work since this is just a network or an extension of the local network as long as the server instance name is correct
(2) Instead of hard coding the server name, I want to create an extra table to be storing the server names so that if the user is using the application for first time then they need to key in the server name which must off course be stored in the table. For example, the table will look like below:
SQlID (PK)
ServerName (Txt)
Path: Driver={ODBC Driver 13 for SQL Server};server=localhost;d
In VBA how do I reference the server name control where it says server=localhost
Currently I always ask the clients to give me the server name and manually type in the connection string in the development computer and compile the app before sending, for sure it does give an error after compiling due to change of the server name, but where it goes it never give any error because it will be pointing to the correct server, for example if the server name is PETER, I will type in the string as:
Server}; server=PETER\SQLEXPRESS; database=WideWorldImporter
(1) Once the ODBC string is done and tested from the developmental computer, that ODBC less DSN File will move with the application where it goes, the only thing to be done is install the correct ODBC version on each work station AND install the database on central place while the work station will either have full Ms Access or Runtime + ODBC.
Questions
(1) What about those who are using VPN can the same ODBC mentioned above work since this is just a network or an extension of the local network as long as the server instance name is correct
(2) Instead of hard coding the server name, I want to create an extra table to be storing the server names so that if the user is using the application for first time then they need to key in the server name which must off course be stored in the table. For example, the table will look like below:
SQlID (PK)
ServerName (Txt)
Path: Driver={ODBC Driver 13 for SQL Server};server=localhost;d
In VBA how do I reference the server name control where it says server=localhost
Currently I always ask the clients to give me the server name and manually type in the connection string in the development computer and compile the app before sending, for sure it does give an error after compiling due to change of the server name, but where it goes it never give any error because it will be pointing to the correct server, for example if the server name is PETER, I will type in the string as:
Server}; server=PETER\SQLEXPRESS; database=WideWorldImporter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dale Fye
You might want to check out my Linked Table Manager for creating DSL-less connections between your application and your SQL Server. It is very easy to use. I've also written an article about it here on EE.