[RESOLVED] ODBC call from Word Userform in VBA

[numblegs]

Hi, I have a userform I was populating with data via an ADODB call (sql string) to an Access database which is updated overnight each day. This works fine under some conditions, but what I really need is an ODBC call to a server so I can get dynamic, up to date data coming from the server to the userform. Can anyone assist with the code for this one?
I know I'll have to use Hostname, accountpath, username and password to get this happening but I've only ever done ADODB stuff before so I'm completely in the dark.
Any assistance greatly appreciated.

[westconn1]

check out the database development forum, or google for connection strings

[si_the_geek]

You don't need to use ODBC, and ideally shouldn't, as OLEDB is newer so is better in several ways. With either, you can/should use ADODB.

You didn't mention what kind of database (Access/SQL Server/...) you want to connect to, but the chances are that the only essential change is the Connection String. You can find examples via the link in my signature.

[numblegs]

OK, thanks...I'll check the link you have provided re connection strings. I believe the server is Universe, which I'm assuming is a unix environment...but I'm not an expert in that field.
Will post as soon as I know more as am not at work right now.
thanks again...

[numblegs]

OK, I've checked Connection Strings but can't see anything for Universe, a Unix based server with a Sloaris OS, which is what I'll need to connect to...
Any thoughts?

[numblegs]

Anyone able to help??
I need to know what sort of connection strings I can use for VBA to a Unix server and whether I can interrogate the server with an sql statement?
Thanks

[westconn1]

i found this

* OLE DB Provider for UniData and UniVerse

oConn.Open "Provider=Ardent.UniOLEDB;" & _
"Data source=myServer;" & _
"Location=myDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword"

For more information, see: Ardent Using UniOLEDB 5.1, Informix Using UniOLEDB 5.2

http://puma.clunet.edu/is/ManualsDoc...3-1005-1_0.pdf
google is wonderful

[numblegs]

Thanks for that connection detail. The issue is now how I go about interrogating the server...I don't think an sql statement is appropriate and there's is very limited info on the web re this.
So if anyone has any ideas on how I get specific data from fields on the server and use it to populate fields on my userform then I would greatly appreciate it.
Thanks very much

[si_the_geek]

What makes you think SQL statements are not appropriate for it?

I have used many different database systems over the years, and have yet to find one that can be used in a better way.


In case you don't know how to run one, here's an example:

    'Connect to the database
Dim objCn as ADODB.Connection
  Set objCn = New ADODB.Connection
  objCn.Open "<your connection string here"

    'Create a recordset so you can get data
Dim objRS as ADODB.Recordset
  Set objRS = New ADODB.Recordset

    'Create an SQL statement to specify the data you want
    ** change this to suit the tables & fields in your database
Dim strSQL as String  
  strSQL = "SELECT Field1 " _
         & "FROM Table1 " _
         & "WHERE Field2 = value"

    'get the data by opening the recordset, using the connection and SQL
  objRS.Open strSQL, objCN, adOpenForwardOnly, adLockReadOnly, adCmdText

    'check if there is any data returned
  If objRS.EOF Then
    MsgBox "There was no data "
  Else
      'use the data that is in the recordset
    Do While Not objRS.EOF
      ** change this to suit the fields in your SQL statement, and to do what you want
      Msgbox objRS("Field1").Value
      objRS.MoveNext
    Loop
  End If

    'when finished with the recordset, close it
  objRS.Close
  Set objRS = Nothing

    'when finished with the connection (and all recordsets that used it), close it
  objCn.Close
  Set objCn = Nothing

If there are parts of this you don't understand, there is a good chance that they are explained by articles in our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[westconn1]

is the server local or on the internet?

[numblegs]

Right, my mistake...I had assumed, obviously incorrectly, that sql wasn't appropriate in this case. I have used sql statements to interrogate my Access db from a Word userform so now that I know I should be totally fine. Many many thanks for sticking with this thread and the excellent advice.
I can now proceed with putting the final touches to my new document automation system and get it launched for all staff to use.
Cheers,
Peter