Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Несколько SQL запросов в одном макросе
 
#1
11.01.2013 14:27:51
Добрый день!
подскажите плз. в VBA не силен, а задачу необх. решить

Суть вопроса- необходимо сделать форму, которая подставлят в шаблоны таблиц данные из БД и приводит все это это в читаемый вид, в файле несолько листов, для заполнения каждого свой sql запрос.
Сейчас для каждого запроса я использую код :


Set cnnConnect = New ADODB.Connection
Set rstRecordset = New ADODB.Recordset
cnnConnect.Open "Provider=XXX.1;Password=XXX;Persist Security Info=True;User ID=XXX;Data Source=XXX"
rstRecordset.Open Source:=SQLStrALL, ActiveConnection:=cnnConnect, CursorType:=adOpenDynamic, LockType:=adLockReadOnly, Options:=adCmdText
With Sheets("Лист1").QueryTables.Add( _
       Connection:=rstRecordset, _
       Destination:=Sheets("Лист1").Range("A3"))
       .Name = "Запрос из базы данных"
       .FieldNames = False
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = False
       .RefreshOnFileOpen = False
       .BackgroundQuery = True
       .RefreshStyle = xlOverwriteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = False
       .RefreshPeriod = 0
       
       .PreserveColumnInfo = True
       
       .Refresh BackgroundQuery:=False
End With

Все работет корректно, но мне не совсем нравится то что для каждого обращения к БД приходится использовать такой громоздкий код.
Прошу подсказать как это оптимизировать
 
 
 
#2
11.01.2013 14:50:39
Может, так?

Код
Set cnnConnect = New ADODB.Connection
Set rstRecordset = New ADODB.Recordset
cnnConnect.Open "Provider=XXX.1;Password=XXX;Persist Security Info=True;User ID=XXX;Data Source=XXX"
rstRecordset.Open Source:=SQLStrALL, ActiveConnection:=cnnConnect, CursorType:=adOpenDynamic, LockType:=adLockReadOnly, Options:=adCmdText
Sheets("Лист1").Range("A3").CopyFromRecordset rs
rs.Close
There is no knowledge that is not power
 
 
 
#3
11.01.2013 14:53:39
Почитайте справку про "QueryTable Object Members" и увидите, что например параметр RefreshOnFileOpen по умолчанию False, поэтому эту строчку можно удалить. Аналогично пройдитесь по остальным параметрам.
Учимся сами и помогаем другим...
 
 
 
#4
11.01.2013 14:54:07
To Johny
Не совсем,
смысл в том что удобнее было бы в рамках этой конструкции использовать несколько "Open Source:=SQLStrALL"
где SQLStrALL это текст запроса в БД, а их несколько
Изменено: Дмитрий --- - 11.01.2013 14:55:56
 
 
 
#5
11.01.2013 15:12:53
Если БД - SQL Server, то можно использовать:
1) Stored Procedure (хранимая процедура);
2) несколько запросов сразу.

Случай 1.
В SQL Server'е необходимо создасть "хранимку":

Код
CREATE PROC GetConsumers
AS

SET NOCOUNT ON;
SEL ECT col1, col2 FROM Table1;
SEL ECT col1, col2 FROM Table2


Случай 2.
Код
SQLStrALL = "SELECT col1, col2 FR OM Table1;SELECT col1, col2 FR OM Table2;"
rs.Opern Source:=SQLStrAll ....


Рекордсеты вычленяются вот как-то так:
Код
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
...

' Выполняем хранимку
conn.GetConsumers rs

' Первый рекордсет
Range("A1").CopyFromRecordset rs
...

'  Второй рекордсет (SELECT col1, col2 FR OM Table2)
Set rs = rs.NextRecordset
Изменено: Johny - 11.01.2013 15:14:59
There is no knowledge that is not power
 
 
 
#6
11.01.2013 15:16:35
Цитата
Johny пишет:
Если БД - SQL Server, то можно использовать:
1) Stored Procedure (хранимая процедура);
2) несколько запросов сразу.

Случай 1.
В SQL Server'е необходимо создасть "хранимку":
Код
 CREATE PROC GetConsumers
AS
SET NOCOUNT ON;
SEL ECT col1, col2 FROM Table1;
SEL ECT col1, col2 FROM Table2


Случай 2.
Код
 SQLStrALL = "SEL ECT col1, col2 FR OM Table1;SELECT col1, col2 FR OM Table2;"
rs.Opern Source:=SQLStrAll ....


Рекордсеты вычленяются вот как-то так:
Код
 Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
...

' Выполняем хранимку
conn.GetConsumers rs

' Первый рекордсет
Range("A1").CopyFromRecordset rs
...

'  Второй рекордсет (SELECT col1, col2 FR OM Table2)
Set rs = rs.NextRecordse

СУБД к сожалению не SQL Server, да и права в БД только на чтение  :(
Изменено: Дмитрий --- - 11.01.2013 15:16:57
 
 
 
#7
11.01.2013 19:47:58
Дмитрий, ну вот ЗАЧЕМ в данном случае полное цитирование?
 
 
 
#8
11.01.2013 23:00:32
Цитата
Дмитрий --- пишет:
смысл в том что удобнее было бы в рамках этой конструкции использовать несколько "Open Source:=SQLStrALL"
где SQLStrALL это текст запроса в БД, а их несколько
Непонятно чего несколько? запросов или баз данных?
-----------------
Если база одна то необходимо понимать структуру обновляемых данных:
Используется ли одинаковый набор данных, и только меняются параметры.
Или все же используются различные наборы данных ?
Если первое то  - пользуемся фильтрами в рекордсете;
если второе то  - используем одно подключение и в цикле меняем строку запроса и выходной диапазон данных
=========
зы... без текстов запроса и строк подключение, больше ничего и не скажешь.
Да и пример Вашего шаблона не помешал бы.
Спасибо
 
 
 
#9
11.01.2013 23:35:29
посмотрите пример,
для вашей базы поменять только строку подключения
Изменено: R Dmitry - 12.01.2013 01:25:47 (масло масленое :))
Спасибо
 
 
 
#10
12.01.2013 13:01:41
Приложил пример
 
 
 
#11
12.01.2013 22:45:56
Читаем очень внимательно, особенно со словом Filter
Спасибо
 
 
 
#12
14.01.2013 14:37:40
Цитата
R Dmitry пишет:
посмотрите пример,
для вашей базы поменять только строку подключения

СПАСИБО!!!

Все получилось)
 
 
 
Страницы: 1
Наверх