Linking to an SQL Server table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Linking to an SQL Server table

Author

Topic

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 10:29:35

Rather than using the upsize wizard in MS Access to connect to tables on a backend SQL server, how would I go about linking an Access Database to an existing table on an SQL server?

Thanks, Phil

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-03 : 10:53:16

using DTS packages you can link acccess tables to sql server.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 11:25:39

What is a DTS package?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-03 : 11:27:08

In your access mdb,
File
Get external Data
Link Tables...
On the Files of Type dropdown box, choose ODBC databases,
choose the DSN you built for connecting to the SQL server,
choose your table(s).

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 11:42:37

Is the DSN setup when I install the server onto the machine, or each time I create a new database/table?

Sorry for all the questions, it's just that i'm a total noob when it comes to this.

Thanks.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-03 : 12:05:29

You (or your admin) have to set it up first time.
Depending on which version of windows, the DSN is typically under control pannel, administrative tools, ODBC (data sources).

The disadvantage of using DSN is that when you copy your access MDB to another client machine, you have to create the same DSN on that machine in order to make those liked tables work for you. So the other alternative is DSNless connection.

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 12:55:35

Well, as you probably guessed, how do I go about setting up a DSNless connection?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-03 : 14:28:32

One way is to use code. In access code module, you use DAO or ADO to select the data, then supply the data to forms/reports as data source.

Here is an example.

On a form load event,
dim strSQL as string
dim rs as adodb.recordset
strSQL="Select * from Orders where ..."
set rs = ExecSQL(strSQL)

--paste following in a code module so it can be shared.

Option Compare Database
Option Explicit

Public Const con_cnStr = "PROVIDER=SQLOLEDB;DATA SOURCE=servername;UID=UserID;PWD=admin;DATABASE=yourDb"

Public Function ExecSQL(strSQL As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim iAffected As Integer

On Error GoTo errHandling:

Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.ConnectionString = con_cnStr
End With
cn.Open
Set rs = New ADODB.Recordset
If UCase(Left(Trim(strSQL), 6)) = "SELECT" Then
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.Source = strSQL
.LockType = adLockOptimistic
.Open
End With
Else 'it is Delete/Insert/Update
cn.Execute strSQL, iAffected, adExecuteNoRecords
'Debug.Print iAffected
End If
Set ExecSQL = rs

Exit_ExecSQL:

Set rs = Nothing
Set cn = Nothing
Exit Function

errHandling:
MsgBox Err.Description
Resume Exit_ExecSQL

End Function

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 17:02:48

And what are the other methods? Is there anything easier?

Phil

NHS
Starting Member

8 Posts

Posted - 2006-04-03 : 21:14:19

Right, I have installed the eval version of SQL Server 2005, and I am trying to upsize a MS database. I am having problems connecting to the server.

The name of the machine is INFOSERV, and in the management studio, i am shown the name of INFOSERV\Administrator.

As far as i can remember, I left authentication upto windows.

Any ideas? Thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-04 : 01:35:29

Are you trying to connect remotely or from the server itself??

if you are trying to connect remotely then in the surface configuration area, enable Remote connections... and then try to login

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

NHS
Starting Member

8 Posts

Posted - 2006-04-04 : 08:24:03

I still can't connect, but am I right in saying that I cant connect to the server remotely because the server is a 180-Day eval version?

Thanks, Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources