| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One connection multiple unique SQL's
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One connection multiple unique SQL's
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 "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One connection multiple unique SQL's
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 <titleQuery from servdb1</title <xml id=docprops<o:DocumentProperties xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="http://www.w3.org/TR/REC-html40" <o:NameQuery from ABCDB</o:Name </o:DocumentProperties </xml<xml id=msodc<odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns="http://www.w3.org/TR/REC-html40" <odc:Connection odc:Type="ODBC" <odc:ConnectionStringDRIVER={IBM DB2 ODBC DRIVER};UID=XXXX;;MODE=SHARE;DBALIAS=ABCDB;</odc:ConnectionString -- Nelson "Sam Wilson" wrote: 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 "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One connection multiple unique SQL's
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One connection multiple unique SQL's
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One connection multiple unique SQL's
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count unique with multiple criteria | Excel Worksheet Functions | |||
| always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
| Multiple queries under same connection | Excel Discussion (Misc queries) | |||
| Sum multiple Unique Values in col | Excel Worksheet Functions | |||
| Use same connection for multiple recordsets | Excel Programming | |||
Linear Mode
