Getting External Data
 

I have an interesting problem to solve and not really sure quite out to do
it. I have a back-end SQL Server Database that has several tables that I
need to pull data from into a single worksheet in Excel. Now, I am not a DBA
guru by any means. I have searched through the discussion group and all of
the VBA material that I own. Can someone point me in the right direction as
to being able to connect to one database and pull data from multiple tables?
The data is laid out where an employee's data is in one table (id, name,
address, city, state, zip) and another has their time charged (id, project #,
time charged) and yet another has their skills, specialties, and the roles
they do. I haven't written any code for this yet so I am just looking for
help with the concept.

Thanks a ton in advance,

Bryan47

Getting External Data
 

This is not my strong area, but these statements worked for me. You will
have to replace items in "<" with whatever from your DBA. Note that
RS.GetRows returns a 2D array of cols by rows. You will need to transpose to
rows by cols.

Dim CM As ADODB.Command
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

Set CN = New ADODB.Connection
CN.ConnectionString = "UID=<Login;PWD=<Password;driver={Microsoft ODBC for
Oracle};SERVER=<Instance;"
CN.CursorLocation = adUseClient
CN.Open

Set CM = New ADODB.Command
Set CM.ActiveConnection = CN

CM.CommandText = "SELECT * FROM <Domain.EmployeeTable"
CM.CommandType = adCmdText

Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.LockType = adLockReadOnly
Set RS.Source = CM

RS.Open
RS.MoveFirst
MyArray = RS.GetRows

RS.Close
Set RS = Nothing
Set CM = Nothing


"Type of Sheet displayed" wrote:

I have an interesting problem to solve and not really sure quite out to do
it. I have a back-end SQL Server Database that has several tables that I
need to pull data from into a single worksheet in Excel. Now, I am not a DBA
guru by any means. I have searched through the discussion group and all of
the VBA material that I own. Can someone point me in the right direction as
to being able to connect to one database and pull data from multiple tables?
The data is laid out where an employee's data is in one table (id, name,
address, city, state, zip) and another has their time charged (id, project #,
time charged) and yet another has their skills, specialties, and the roles
they do. I haven't written any code for this yet so I am just looking for
help with the concept.

Thanks a ton in advance,

Bryan47

Getting External Data
 

hi
simpliest way may be to use MSQ. on the 2003 menu...
toolsGet external datanew database queryfollow the wizard.
you may have to get IT to help if passwords are an issue.
from experience, i would also suggest that you create a db in access, link
your server tables to the access db, write your queries in access then link
to the access querry via MSQ(microsoft query). it would seem like a round
about way to do it but access is more powerfull than MSQ, for versital. in
the past, i had trouble with a two table MSQ.s mostly with the joins. MSQ
wouldn't do left and right joins but access would do any kinds of join. and
linking to the access query via MSQ was easier that setting up a 2 table MSQ.
you could also look into ADO but unless you are a fair programmer, i would
stick to MSQ.

regards
FSt1

"Type of Sheet displayed" wrote:

I have an interesting problem to solve and not really sure quite out to do
it. I have a back-end SQL Server Database that has several tables that I
need to pull data from into a single worksheet in Excel. Now, I am not a DBA
guru by any means. I have searched through the discussion group and all of
the VBA material that I own. Can someone point me in the right direction as
to being able to connect to one database and pull data from multiple tables?
The data is laid out where an employee's data is in one table (id, name,
address, city, state, zip) and another has their time charged (id, project #,
time charged) and yet another has their skills, specialties, and the roles
they do. I haven't written any code for this yet so I am just looking for
help with the concept.

Thanks a ton in advance,

Bryan47