Connectiing Access 2003 to Sql Server 2005 Express

[Cheryl]

Hi everyone,
How can I connect Access 2003 to Sql Express 2005 ?
Thanks
Cheryl

 

[Jim Bunton]

Hi everyone,
How can I connect Access 2003 to Sql Express 2005 ? Thanks
Cheryl

One way is

to create linked tables within Access

You can them use them more or less as if they are access tables.

Below is a Sub which does the connecting by creating a a new tableDef which
with appropriate mods you can call from within a loop of another sub to
connect each of a list of tables.

[Each time you start up you must delete all these linked tabledefs BEFORE
you redefine them

cdb.TableDefs.Delete TableList!LinkTableName

you can store a list of the linked tables in a table! - and put the line
above in a loop that runs dowmn the table list]


Sub CreateTableDefLinkToServer(TableName As String, Uid As String, Pwd As
String, ErrList As String)
On Error GoTo Err_CreateTableDefLinkToServer
Dim cdb As Database, NewTableDef As TableDef
Dim ServerTablePrefix As String, ConnStr As String

'ShowProgress "Link " & TableName
ServerTablePrefix = "dbo." '!personalise
Set cdb = CurrentDb
Set NewTableDef = cdb.CreateTableDef(TableName)
If Not (NewTableDef.Properties(5) And dbAttachSavePWD) Then
NewTableDef.Properties(5) = NewTableDef.Properties(5) +
dbAttachSavePWD
End If
NewTableDef.SourceTableName = ServerTablePrefix & TableName

ConnStr = "ODBC;DRIVER=SQL
Server;SERVER=thenameoftheODBC;APP=Microsoft®Access;DATABASE=thesqlserverdatabasename;Network=DBMSSOCN;Address=nnn.nnn.nnn.nnn,nnnn"
'thenameoftheODBC you must create this > control Panel, Administrative Tools

Data Sources (ODBC)

'nnn.nnn.nnn.nnn is a valid ip address
'I can't for the life of me remember what nnnn is google odbc connect
strings

ConnStr = ConnStr & ";UID=" & Uid & ";PWD=" & Pwd
NewTableDef.Connect = ConnStr
cdb.TableDefs.Append NewTableDef
'ShowProgress "Link " & TableName & " succeeded"
Exit_CreateTableDefLinkToServer:
Exit Sub

Err_CreateTableDefLinkToServer:
ShowProgress "Link " & TableName & " failed"
If Len(ErrList) < 800 Then
ErrList = ErrList & TableName & " - Error cannot Connect at Server"
& vbNewLine
ErrList = ErrList & " " & Err & " " & Err.Description & vbNewLine
Else
If Right(ErrList, 12) <> "" Then
ErrList = ErrList & vbNewLine & "AND other errors"
Else
'leave errlist asis
End If
End If
Resume Exit_CreateTableDefLinkToServer

End Sub


Well - good luck!

Jim Bunton

 

[Susie DBA [MSFT]]

upgrade to Access 2007; sorry M$ screwed us with SQL 2005

 

[Tony Toews [MVP]]

A a r o n K e m p f wrote:

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Susie DBA [MSFT]]

note that Susie is correct and Tony is a big fat old lazy retard