ODBC connection trough Visual Basic in Excel 2003

This is a duplicate post of a question I posted in the Excel Programming
newsgroup. Due to a lack of replies, I figured this might be a better place
to post the question.

Hey guys!
I've searched the forum a bit for an answer to this, but none of the
questions asked so far would give an answer to my question. So here goes!

How do you connect to a database, in this case REMEDY trough an AR Ssytem
ODBC Data Source, query the database for a table and grab everything in the
selected columns from X date to Y date into a defined worksheet?

It's setup in the ODBC sourceadmin with the following info:

Data Source Name: AR System ODBC Data Source
AR Server: REMEDY
Username: myUsername
Password: (blank)

Options: Replace '.' in object names is checked.


The table that should be queried for the info is called "HDQueries" and only
10 of the columns in the table are needed, the sheets name is "DataDump" and
the insert will start at A1 and go on til it's done.

One of the issues I'm having is that I can't store my username and password
in the code. A dialog of sorts must pop-up with a request for a valid
username and password for the connection. The second is that I'm a bit over
my head on how to program this. ^_^ I have rudamentary VB skills, so if
someone can help me trough this, the rest should be somewhat easier.

--
Cato Larsen
HelpDesk Monkey

"Cato Larsen" <CatoLarsen@discussions.microsoft.com> wrote in message
news:24E7C9AF-3036-4422-900E-547AE11ED846@microsoft.com...
> This is a duplicate post of a question I posted in the Excel Programming
> newsgroup. Due to a lack of replies, I figured this might be a better
place
> to post the question.
>
> Hey guys!
> I've searched the forum a bit for an answer to this, but none of the
> questions asked so far would give an answer to my question. So here goes!
>
> How do you connect to a database, in this case REMEDY trough an AR Ssytem
> ODBC Data Source, query the database for a table and grab everything in
the
> selected columns from X date to Y date into a defined worksheet?
>
> It's setup in the ODBC sourceadmin with the following info:
>
> Data Source Name: AR System ODBC Data Source
> AR Server: REMEDY
> Username: myUsername
> Password: (blank)
>
> Options: Replace '.' in object names is checked.
>
>
> The table that should be queried for the info is called "HDQueries" and
only
> 10 of the columns in the table are needed, the sheets name is "DataDump"
and
> the insert will start at A1 and go on til it's done.
>
> One of the issues I'm having is that I can't store my username and
password
> in the code. A dialog of sorts must pop-up with a request for a valid
> username and password for the connection. The second is that I'm a bit
over
> my head on how to program this. ^_^ I have rudamentary VB skills, so if
> someone can help me trough this, the rest should be somewhat easier.
>
> --
> Cato Larsen
> HelpDesk Monkey


Your question covers several issues: (That's just a prelude to warn you are
about to receive an usatisfactory reply. <g>)

An AR Remedy Server can be configured to use several core databases, from a
flat file (eg. mdb) to a RDBMS (eg. Oracle). You need to findout which data
store the AR Remedy Server is using. (More on this later.)

Second, an AR Remedy Server provides both an ODBC driver and an API. You can
use both to access the data. If using the ODBC driver then you need to pick
a "data access library" that your VB app can use with the driver to access
the data. In your case you can probably use DAO with the Remedy server. As
you are not a programmer using the API is perhaps too much at this time.

However, their ODBC driver is limited. ie, doesn't support all features and
can be amazingly slow. So you may be better off attempting to connect
directly to the data store. For example, if it uses Oracle you can use the
Oracle OLE DB Providers and ADO. If using mdb (MSAccess) then the Jet
provider with DAO will probably work best. [This also allows you to use a
DSN-less connection which may provide more control.]

For right now look up DAO in the Excel VBA Help, and connect using the DSN
as shown in the help. Post back if you get stuck.

[Note: Many of their examples will show using MSAccess, but it doesn't
matter - the code is essentially the same, no matter what data store.]

Normally if you leave the Password field blank in the DSN and it's required
to open the database - a dialog will appear when the connection is requested
that will allow the user to enter it. But this behavior is "driver/provider"
dependent. Try it and see what happens.

hth
-ralph

"Ralph" <nt_consulting64@yahoo.com> wrote in message
news:OPR3R1WdIHA.4144@TK2MSFTNGP05.phx.gbl...
>

This might help to get you started...
[Warning Air Code!]

Dim ws As Workspace
Dim db As Database
Dim strConn As String

' The "DSNName" is what ever the dsn appears as
Set ws=DBEngine.Workspaces(0)
strConn = "ODBC;DSN=" & DSNName & ";UID=" & _
UserName & ";PWD="
Set db=ws.OpenDatabase("", False, False, strConn)

'''''
Now here is where we need more information.
You give the name of the table, but not that fields. You only say you need
'10' of them. You will need to create a SQL Query that looks something like
this...
Dim sSQL As String
sSQL = "SELECT fld1, fld2, fld3, fld4, ... FROM HDQueries"
'''''''''
Dim rs As Recordset
Set rs=db.OpenRecordset(sSQL)

'Transfer the data to Excel
Sheet.Range("A1").CopyFromRecordset rs

Hello Ralph!
Due to a bug with the MS Websites, it's taken me sometime to reply to this
issue.

Thank you for helping me out on this. I've gotten a way from last time I
posted, but a new issue has come up.
The query won't return any data, no matter what kind of select string Im
passing into it. Could you check out if I've done something wrong here:

Note: I have to manualy write the code from one screen to the other (diff
comps) so please don't mind any var names with some jumbled up letters and
the likes.

Dim conData as ADODB.Connection
Dim rsQuery as ADODB.Recordset

set conData = new ADODB.Connection
set rsQuery = new ADODB.Recordset

Dim uName as string
Dim uPass as string

' Here I load a form for username and password input into the strings

Dim strConnect as String
strConnect = "Provider=MSDASQL.1;Password=" + uPass + ";Persist Security
Info=true;User ID=" + uName + ";Data Source=AR System ODBC Data
Source;Mode=Read"

Dim sSQL as String
sSQL = "Select * FROM Henvendelse WHERE Kategori1 = 'Network'"

conData.ConnectionString = strConnect
conData.Open

With rsQuery
..ActiveConnection = conData
..Open sSQL
if(rsQuery.EOF) Then
MsgBox("rsQuery is empty!")
else
ActiveWorkbook.Worksheets("Data Dump").Range("A2").CopyFromRecordset rsQuery
end if
..Close
End with

rsQuery.EOF is true no matter what query I've done.
The example query should return some 30 000 records at least, if not more.
I've tried more and less specific queries but with the same results.

Any ideas?
--
Cato Larsen



"Ralph" wrote:

>
> "Ralph" <nt_consulting64@yahoo.com> wrote in message
> news:OPR3R1WdIHA.4144@TK2MSFTNGP05.phx.gbl...
> >
>
> This might help to get you started...
> [Warning Air Code!]
>
> Dim ws As Workspace
> Dim db As Database
> Dim strConn As String
>
> ' The "DSNName" is what ever the dsn appears as
> Set ws=DBEngine.Workspaces(0)
> strConn = "ODBC;DSN=" & DSNName & ";UID=" & _
> UserName & ";PWD="
> Set db=ws.OpenDatabase("", False, False, strConn)
>
> '''''
> Now here is where we need more information.
> You give the name of the table, but not that fields. You only say you need
> '10' of them. You will need to create a SQL Query that looks something like
> this...
> Dim sSQL As String
> sSQL = "SELECT fld1, fld2, fld3, fld4, ... FROM HDQueries"
> '''''''''
> Dim rs As Recordset
> Set rs=db.OpenRecordset(sSQL)
>
> 'Transfer the data to Excel
> Sheet.Range("A1").CopyFromRecordset rs
>
>
>
>

HOW CAN I CONNECT AND BEFORE CONNECT WHAT ARE THE PROCEDURE WE HAVE TO DO?