ODBC Connections 1

[rpbenito]

I have this code here...it seems to work...how do I check the results of my query....how can I print them to the screen...thanks




Set wrk = CreateWorkspac("", "rbenito", "rbenito",dbUseODBC)
Set conn = wrk.OpenConnection(inst, , , "ODBC;DATABASE=all_tab_columns;UID=rbenito;PWD=rbenito;DBQ=" & inst)
MsgBox "Connection Established"
Set dbs = wrk.OpenDatabase("drep", dbDriverNoPrompt, True,"ODBC;DATABASE=all_tab_columns;UID=rbenito;PWD=rbenito;DBQ=drep&quot
Set qrydef = conn.CreateQueryDef("&quot
qrydef.SQL = "Select owner from all_tab_columns"
dbs.Execute ("Select owner from all_tab_columns&quot

 

[Cagliostro]

you should specifu also the driver name Driver={driovername}, in the case if database is not registered in ODBC data sources John Fill

ivfmd@mail.md

 

[Aivars]

Hi!

More easily work with tables of ODBC is to create links to this tables and then to work with them as with Access tables. In such case you can use all Access tools for creating of forms, reports, queries etc.

Example from my application (ODBC->>> Informix DB):
In such case I already have links to tables (I created its before by using wizard ->> it's possibly to create links to MS Access DB tables what have same tables as ODBC and later to refresh (change) its). Following codes refresh (or change) before created links.

Sub LinksRefresh()
On Error GoTo Err_LinksRefresh
Dim tbf As TableDef
Dim i As Byte

'On Error Resume Next
For Each tbf In CurrentDb.TableDefs
'If tbf.Connect <> &quot;&quot; Then 'For first connection to ODBC tables
If Left(tbf.Connect, 4) = &quot;ODBC&quot; Then 'Next connections

tbf.Connect = &quot;ODBC;DSN=baltcdbi;DB = baltcdb;Host = server;Serv = lat;SRVR = lat_online;Pro = onsoctcp;UID = vdd;PWD = vddvdd;&quot;
tbf.RefreshLink
'Debug.Print tbf.Name & &quot; >>> &quot; & tbf.Connect
End If
Next tbf


Err_LinksRefresh:
If Err.Number = 3151 Then
MsgBox &quot;ODBC--connection failed..&quot; & vbLf & _
&quot;Inform your DB administrator about them.&quot;, vbCritical
docmd.quit '???????
End If
End Sub

Aivars

 

[rpbenito]

Thanks that was really helpful.......would it be possible to do all of this by having the user enter the DSN name and DB name and doing all of the linking dynamically? Or is there a way to do what you described above using VBA? thanks

 

[Aivars]

Hi again!

In some cases like this I created special table for saving of tabl names, paths, DSN etc. Then on start of application I run codes what create recordset based on this special table.

I'm improvising following codes, but maybe its will little help you:

private sub form_load()
dim rst as recordset
dim ConnectString as string
set rst=&quot;Select * From SpecTable;&quot;

with rst
while not .eof
ConnectString =&quot;ODBC;DSN=&quot; & !DNSfield & &quot;;DB = &quot; & !DBfield & &quot;;Host = &quot; & !HostField & &quot;;Serv = &quot; & !ServFieldlat & &quot;;SRVR = &quot; & !SRVRfield & &quot;;Pro = &quot; & !ProField & &quot;;UID = &quot; & !UIDfield & &quot;;PWD = &quot; & !PWDfield
'For UID and PWD you can create inputbox or take any other object
CurrentDb.CreateTableDef !TableNameField, dbAttachedODBC, !TableNameField, ConnectString 'I'm not sure that this command row is valid - you may check it and compare with Help
wend
end with
rst.close
set rst=Nothing
end sub

Good luck!
Aivars

 

[Dodge]

Thanks for the help........