Remote SQL Server link

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

You should use OLEDB provider rather than using ODBC (old technology now). A connection string using the OLEDB provider will look like this:

Provider=SQLOLEDB.1;Password=pkmsqa;Persist Security Info=True;User ID=pkmsqa;Initial Catalog=MANH2004R20DATA;Data Source=ganesha

Initial Catalog is the name of the database.

Data Source is the name of the instance

User ID is the SQL Server login-id and the Password field has the password.

If you do want to use ODBC DSN, then you can create a system DSN using:

Server: Name of the instance

LoginID and Password - the authentication information. And then you can use the DSN in your connection string.

Alan Ruff SSC Journeyman Points: 89 More actions · Answer 2

Thanks for your reply rsharma.

I've never done this before so I do not understand where I would use the code you suggest.

In access I have created a new ADP project. The wizard tries to create a connection and it doesn't connect.

Is this the OLEDB connection or ODBC connection?

Or, how do I start on this?

I need to be able to see the tables on the SQL server.

Alan Ruff SSC Journeyman Points: 89 More actions · Answer 3

OK, I figured it out.

I opened a new Access file. Selected a Project (existing data).

The fact that I have selected a Project indicates that it will use OLE DB.

So I entered the server name, login name, and password and I'm there.