One connection multiple unique SQL's

N

Nelson

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
 
S

Sam Wilson

Without seeing your code, you want something along these lines:


sub demo()


dim c as adodb.connection
dim rs as adodb.recordset
dim s as string

set c = new adodb.connection
c.Open "Provider= MSDASQL;Data source = {ODBC DSN name};Initial Catalog =
{your db};" & _
"user id={id};password = {pwd}"

'1st query
s = "select ..."
set rs = c.execute(s,,1)
..
..
..
rs.close

'2nd query
s = "select ..."
set rs = c.execute(s,,1)
..
..
..
rs.close

'etc

c.close

end sub
 
N

Nelson

Thanks I am having problems defining the catalog and making sure the provider
is correct

If I export the connection this is what I see, does this help?

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=ODBC>
<title>Query from servdb1</title>
<xml id=docprops><o:DocumentProperties
xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>Query from ABCDB</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:eek:dc="urn:schemas-microsoft-com:eek:ffice:eek:dc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DRIVER={IBM DB2 ODBC
DRIVER};UID=XXXX;;MODE=SHARE;DBALIAS=ABCDB;</odc:ConnectionString>
 
N

Nelson

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?
 
N

Nelson

Also how will this work with the way my existing 10 worksheets are already
formatted?

I want to keep the worksheets the way they are as there is a summary sheet
that pulls all that data together .

Thanks
 
P

Patrick Molloy

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

======================================================================
 
N

Nelson

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
 
N

Nelson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embed the same Excel multiple times 0
Import multiple Access Queries 0
Access 2010 DB not fuctional with multiple users 2
SQL not importing records from ACCDB 2
Upload Excel data to Access error 1
Get results from Access Database. 2
Accessing the Data Connection Strings and Command Text (SQL) in VB 5
Accept & Use ODBC Login 3

Top