Create ODBC Connectoin

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an MS Access application which needs to read/update a table on a
separate SQL server.

With the appropriate security, I can link that table to my application, but
my application calls to this table only works on workstations which have the
ODBC connection defined in the ODBC Datasource Administrator (odbcad32.exe).

What VBA code can I use in my app to create the configuration on the fly
when I detect it's not there (e.g., by error logic when attempting to open
the table)? Is this even the best solution?

I've searched around for ODBC posts and am a little bit over my head with
this (I'm self taught). I found a lot of code that connects to ODBC objects
but they all seem depend on that System DSN set up.

Thanks in advance for help on this--I'm sure it's probably much
simpler/easier than I'm trying to make it. Chris
 
KitCaz said:
I have an MS Access application which needs to read/update a table on
a separate SQL server.

With the appropriate security, I can link that table to my
application, but my application calls to this table only works on
workstations which have the ODBC connection defined in the ODBC
Datasource Administrator (odbcad32.exe).

What VBA code can I use in my app to create the configuration on the
fly when I detect it's not there (e.g., by error logic when
attempting to open the table)? Is this even the best solution?

I've searched around for ODBC posts and am a little bit over my head
with this (I'm self taught). I found a lot of code that connects to
ODBC objects but they all seem depend on that System DSN set up.

Thanks in advance for help on this--I'm sure it's probably much
simpler/easier than I'm trying to make it. Chris
Click to expand...

I use this code...

Sub DSNLessTableLink()

Dim db As Database
Dim tbl As TableDef
Dim tblName As String
Dim srcTblName As String
Dim Conn As String

tblName = "NameOfLocalLink"
srcTblName = "NameOfTableOnServer"

Conn = "ODBC;" & _
"Driver=SQL Server;" & _
"Database=DataBaseName;" & _
"Server=ServerNameOrIP;" & _
"Trusted_Connection=Yes;"

Set db = CurrentDb

Set tbl = db.CreateTableDef(tblName)
tbl.SourceTableName = srcTblName
tbl.Connect = Conn
db.TableDefs.Append tbl
db.TableDefs.Refresh

Set tbl = Nothing
Set db = Nothing

End Sub
 
Had to throw this together so bear with me. Create an ADO connection using
code.
No DSN is necessary.


Dim strConnectionString As String 'ADO Connection String
Dim oCn As ADODB.Connection 'ADO Connection(0)
Dim oRs As ADODB.Recordset 'ADO Recordset(0)
Dim oCmd As ADODB.Command'ADO Command
Dim oCn1 As ADODB.Connection 'ADO Connection(1)
Dim oRs1 As ADODB.Recordset 'ADO Recordset(1)
Dim strCmdString As String 'ADO Command String
Dim strServer as String
Dim strDatabase as String
Dim intTimeoutInterval as Integer

intTimeoutInterval = 60 'seconds
'Using NT authentification

strConnectionString ="DRIVER={SQL
Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabasename"

Set oCn = New ADODB.Connection
Set oCmd = CreateObject("ADODB.Command")
oCn.Open strConnectionString
Set oCmd.ActiveConnection = oCn
oCmd.CommandTimeout = intTimeoutInterval

'Run a select query to cycle trhough all the records:
strCmdString = "SELECT * FROM table1"
oCmd.CommandText = strCmdString
Set oRs = oCmd.Execute
Do while not oRs.EOF
'Process records here
ors.Movenext
Loop

'OR
'Run an update query
strCmdString = "UPDATE table1 SET Field1 = 'ABC'"
oCmd.CommandText = strCmdString
Set oRs = oCmd.Execute

oRs.Close
oCn.Close
Set oRs = Nothing 'Garbage Collection / Return to heap
Set oCmd = Nothing 'Garbage Collection / Return to heap
Set oCn = Nothing 'Garbage Collection / Return to heap

Make the necessary substitutions for User, Password, database name, Server
name, Table/View name, etc.

Hope this helps!

jmonty
 
Rick, thanks so much for this.

It worked like a charm except I forgot to mention that I didn't want to have
to guarantee that the users of my application to have security for the SQL
server in question. I thought to pass a user/password in this string, so I
used "UID=userid;" and "PWD=password;" parameters with my ID/pswbut the app
still attempts to connect with the app user's ID.

The only thing I can think of is that my security is based on my Windows
account; is this failing because I don't have a service account/an account
validating against SQL directly and not through Windows? I've requested a
service account but haven't gotten one yet. :)

Chris
 
Ok, I got a service account/password, and the same thing happens. My
application's attempts to read the table still try to validate with the
current user's account. There must be a way to do this, right? What am I
doing wrong?
 
Back
Top