MS Access Appln connecting to SQL Server2005 DSN-less

0
votes

I am a newbie trying to connect a MS Access Appln (currently connected to an external Access database) to a remote SQL Server 2005. To make it easier to distribute to a large audience want to make it DSN-less.

I could connect to local Access db with following connection string

DbFullName = CurrentProject.Path & "\" & "project_DB.accdb"
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbFullName & ";Jet OLEDB:Database Password=pwd;"

for the SQL Server 2005 connection, when i tried following command, 

"Data Source=myserverdev;Initial Catalog=project_DBSQL;Persist Security Info=True;User ID=msaccess_cbd;Password=Paci0113"
providerName = "System.Data.SqlClient"

I get the error-

datasourcename not found and device driver not found

I tried to get connected with SQL server through DSN-less connection through VB but I failed.The code which I tried is:-

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\IP address\databasename;User ID=username;Password=pwd;

error message i got was :

end of line expected

Please suggest me some way to solve this.

1
sql-servervbaconnection-string
See stackoverflow.com/questions/14186675/… and connectionstrings.com - Fionnuala

1 Answers

1
votes

System.Data.SqlClient looks like an ADO*.NET* provider. You need to use an ADO/OLEDB provider when coding in VBA. Here's a connection string for the old OLEDB provider:

"Provider=SQLOLEDB.1;Data Source=myserverdev;User ID=msaccess_cbd;Password=Paci0113;Initial Catalog=project_DBSQL"

Some points:
1) You better change that password now that you've told the world about it.
2) You don't need to bother with the "Persist.." clause.
3) That SQLOLEDB provider has been around for a while. It was well distributed with MDAC and you are likely to find it on many client machines but it has some limitations:
- It is less capable of connecting to newer versions of SQL Server particularly when running side by side on the same machine with other versions of SQL Server. You end up having to add port numbers into the connection string.
- It will not support newer SQL language features that newer server versions have.
4) There are newer providers ('Native Client' for 2005, 'Native Client 10' for 2008 etc) but you should check if they've been distributed to all your PCs.

Here is a page with connection strings for Native Client (9 / 2005):
http://www.connectionstrings.com/sql-server-2005#sql-server-native-client-9-0-oledb-provider