Setting up ODBC via code

  • Thread starter Thread starter acs68
  • Start date Start date
A

acs68

Hi all,

Hoping someone can answer a couple of questions for me. I have found some
code on the Microsoft website to set up dsnless connections. I've inserted
the code below. I am at a bit of a loss as to when to use the code. I have
10 tables that I would like to attach to.

At what point do I run the code - only once ? or each time the database
loads ?

If anyine could explain this to me I'd really appreciate it.

Also, is it possible to simply create the ODBC connection via code and have
it automatically appear in the ODBC Administrator (in Control Panel).

thanks in advance,
A
dam


------START CODE------
Private Sub Form_Load()
MousePointer = vbHourglass
Dim strConnect As String
' Change the next line to reflect your driver and server.
strConnect = "driver={SQL Server};server=jonfo5;" & _
"database=pubs;Username=<username>;PWD=<strong password>;"
Set en = rdoEngine.rdoEnvironments(0)
Set cn = en.OpenConnection( _
dsName:="", _
Prompt:=rdDriverNoPrompt, _
ReadOnly:=False, _
Connect:=strConnect)
cn.QueryTimeout = 600
MousePointer = vbNormal
End Sub
Private Sub Command1_Click()
MousePointer = vbHourglass
Dim rs As rdoResultset
Set rs = cn.OpenResultset(NAME:="Select * from authors", _
Type:=rdOpenForwardOnly, _
LockType:=rdConcurReadOnly, _
Options:=rdExecDirect)
Debug.Print rs(0), rs(1), rs(2)
MousePointer = vbNormal
End Sub
-----------END CODE_--------------------
 
My advice would be to try in the load event as per the MS code. If you need
it somewhere else, when a routine runs a requery for example, then that will
tell you that it needs to be at the form level at which point you'll need to
start playing with the position of your declarations. Decent error handling
in your code should allow you see where it's necessary.

And yes, you could set up a connection that shows in the ODBC administrator
(probably user instead of system... down to rights). An ODBC connection is,
in fact, just a bunch of registry settings. Export a .reg file with your db
and you could, I think, include a shell command to import those settings to
a machine running your db.
 
Back
Top