Thanks Patrick, I am very new at doing this and could use a bit more assistance

1st thing is my query's (example here) all have a "GROUP" criteria in it
that excel does not seem to like, I am assuming because of the ""

LoadFromSQL "SELECT XXX_BASS.XXXNO, XXX_BASS.SEVERITY, XXX_BASS.PRIORITY,
XXX_BASS.STATUS, XXX_BASS.ABCD, XXX_BASS.PRODID, XXX_BASS.COUNTRY,
XXX_BASS.BNO, XXX_BASS.CNO, XXX_BASS.CUSTNAME, XXX_BASS.CUSTNO,
XXX_BASS.DAYSOPEN, XXX_BASS.OPENTOCT, XXX_BASS.USERID, XXX_BASS.SGDATE,
XXX_BASS.USERGROUP, XXX_BASS.OPENDATE1, XXX_BASS.CLOSEDDATE1,
XXX_BASS.COMMENT, XXX_BASS.REL, XXX_BASS.TEAM, XXX_BASS."GROUP" FROM
NC.XXX_BASS XXX_BASS WHERE (XXX_BASS.OPENDATE1{d '2005-01-31'}) AND
(XXX_BASS.REL Between '600' And '699') AND (XXX_BASS.PRODID In
('5724NDDDD','5724EEEEE'))ORDER BY XXX_BASS.CUSTNO", Worksheets("6XX")

2nd - I am unsure what to put here

..Open "PROVIDER=MSDASQL;driver={SQL"
Server};server=Patrick-PC;uid=;pwd=;database=MyDatabase;"

This is what I have for connection information from the connection profile
in excel.

DRIVER={IBM DB2 ODBC DRIVER};UID=;;MODE=SHARE;DBALIAS=SEVEN;

In excel my connection properties name is Query from SEVEN


- The last section you had outlined I am lost on as well

Sub LoadFromSQL(sql As String, ws As Worksheet)....



Thanks in advance for your assistance
--
Nelson
--
Nelson


"Patrick Molloy" wrote:

create one connection - make the object common to the module, and you can
either create 10 recordsets, or call a function 10 times - once per sheet.
In this example the "scope" of the variables db and rst are the module ...so
the db can be connected to in a separate call and be available later when
populating the recordset

"Main" in the procedure that first makes the database connection and then
calls the data fetch roeutine numerous times.
Should be easy enough to follow...

============================================
Option Explicit
Dim rst As ADODB.Recordset
Dim db As Connection

Sub Main()
connectToDB

LoadFromSQL "select region from products ", Worksheets("sheet1")
LoadFromSQL "select productname,unitsinstock from products",
Worksheets("sheet2")
LoadFromSQL "SELECT somethingelse from table", Worksheets("sheet3")

db.Close
Set db = Nothing

End Sub

Sub connectToDB()
Set db = New Connection
With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=Patrick-PC;uid=;pwd=;database=MyDatabase;"
End With
End Sub

Sub LoadFromSQL(sql As String, ws As Worksheet)
Dim i As Long
Set rst = New Recordset
rst.Open sql, db, adOpenStatic, adLockOptimistic

' load the data to passed worksheet
ws.Range("B5").CopyFromRecordset rst

rst.Close
Set rst = Nothing
End Sub

================================================== ====================


"Nelson" wrote in message
...
Maybe I should clear up what I mean,

I have the spreadsheet the way I want it with 10 tabs pulling 10 different
types of data from the same DB.

I see I have 10 different connections XXXDB1 - 10 signifying the 10
different queries.

I cant have save the password, so I want to know how can I have 1
connection
request (say XXXDB1) that will then enable all 10 SQLs to update their
respective tabs?





--
Nelson


"Nelson" wrote:

Good day, in creating a my worksheets that have differing DB queries I
noticed that I have also created a different connection string each time.

Now when I update my 10 work sheets I am prompted for my password and DB
details 10 times

All connections are to the same DB only the queries are different, how
can I
make it so that I am only asked once for the DB information then all the
sheets get updated?

Thanks
--
Nelson
--
Nelson