connect to a SQL Server table through code?

[eric]

Hi!

I have an Access db that I need to connect to a SQL Server table. I'd like
it run from several clients and I don't want do create a ODBC-connection on
every client. Is it possible to connect through code and does anyknow how to
do that?

Thx!

/e

 

[Stefan Hoffmann]

hi Eric,

I have an Access db that I need to connect to a SQL Server table. I'd like
it run from several clients and I don't want do create a ODBC-connection on
every client. Is it possible to connect through code and does anyknow how to
do that?

Really? Or would it be sufficent to connect them without using a DSN,
which is called DSN-less connection:

http://www.connectionstrings.com/?carrier=sqlserver

Just create your linked manually:

With CurrentDbC
.TableDefs.Append _
.CreateTableDef(ADestinationName, 0, _
ASourceName, CONNECTION_ODBC)
End With

mfG
--> stefan <--

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

DAO is not reccomended; you shoudl be using Access Data Projects

 

[SmartbizAustralia]

All you have to do is to link the tables in SQL on your pc (via that
odbc settings) and then run a vba routine through to reset the link so
it doesn't rely on the ODBC setting!

Public Sub FixODBCConnection()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Left(tdf.Connect, 4) = "ODBC" Then
tdf.Attributes = DAO.dbAttachSavePWD
tdf.Connect = "ODBC;DRIVER={SQL
Server};SERVER=ServerName;DATABASE=DatabaseName;UID=LoginName;PWD=password;"
tdf.RefreshLink
End If
Next
End Sub

Tom Bizannes
Microsoft Access Development
Sydney,Australia

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[Robert Morley]

Why is it that my Blocked Senders list now contains 9 different e-mail
addresses, ALL for Aaron Kempf...and not a single other person? I mean,
seriously Aaron, do you not get the message that if you send offensive posts
to a newsgroup, your posts won't be welcome...WHATEVER e-mail address you
use?!? What is the point of changing your e-mail address, other than to get
a small handful of messages through, which everybody who's ignoring you will
continue to ignore anyway, and then block your new address?



Rob

message

<snipped>

 

[IRS Intern]

Rob

maybe if you and your other MDB butt-buddies would stop dis-crediting
me.. and stop spamming people to not listen to me-- then maybe I
wouldn't be forced to use these measures.

Maybe if Microsoft wouldn't block my posts on a public server-- then
maybe you woudln't need to go through the trouble

ever thought about that? ?

 

[IRS Intern]

I don't send offensive messages if you kids got a clue and used ADP
and stopped spreading lies

 

[Tom Wickerqueer]

just use Acess Data Projects and you don't have to deal with any of this
hassle