SQL에서 조회할 매개 변수를 전달하는 방법(Excel)

SQL에서 조회할 매개 변수를 전달하는 방법(Excel)

Excel을 SQL에 "링크"했더니 정상적으로 동작했습니다.SQL 스크립트를 작성했는데 잘 동작했습니다.제가 원하는 것은 파라미터를 쿼리에 전달하는 것입니다.새로 고칠 때마다 파라미터(필터 조건)를 SQL Query에 전달할 수 있습니다."연결 정보"에서 매개변수 단추가 사용 불가능합니다.그래서 매개 변수 쿼리를 만들 수 없습니다.누가 나를 도와줄 수 있나요?

이 게시물은 오래되어 OP에는 별로 도움이 되지 않을 것입니다만, 저는 이 질문에 답하기 위해 오랜 시간을 소비했기 때문에, 저의 조사 결과를 갱신하려고 생각했습니다.

이 답변은 Excel 문서에서 이미 SQL 쿼리를 실행하고 있다고 가정합니다.웹에서 이 작업을 수행하는 방법을 보여 주는 튜토리얼이 많이 있으며, 기존의 OLE DB 쿼리에 대해 작동하는 것 같지 않다는 점을 제외하고 매개 변수화된 쿼리를 추가하는 방법을 설명하는 튜토리얼도 많이 있습니다.

나처럼 작업 쿼리를 사용하여 기존 Excel 문서를 건네받았지만 사용자가 데이터베이스 필드 중 하나를 기준으로 결과를 필터링하고 싶어하고, 나처럼 Excel도 SQL 전문가도 아닌 경우 도움이 될 수 있습니다.

이 질문에 대한 대부분의 웹 응답에서는 Excel에서 커스텀 파라미터를 입력하도록 요구받으려면 쿼리에 "?"를 추가하거나 프롬프트 또는 셀 참조를 파라미터가 있어야 하는 [brackets]에 배치해야 한다고 말합니다.이것은 ODBC 쿼리에 대해서는 동작할 수 있지만 OLE DB에는 동작하지 않는 것 같습니다.전자의 인스턴스에서는 "1개 이상의 필수 파라미터에 대해 값이 지정되지 않았습니다"를 반환하고, 후자의 2개의 인스턴스에서는 "Invalid column name 'xxxxx' 또는 "Unknown object 'xxxxxxxxx'를 반환합니다.마찬가지로 "파라미터…" 또는 "쿼리 편집…" 버튼도 사용할 수 없습니다.이 경우 Excel 2010을 사용하고 있습니다만, Excel 97-2003 워크북(*.xls)을 사용하고 있습니다.

단, 파라미터 셀과 간단한 루틴을 사용하여 쿼리 텍스트를 프로그래밍 방식으로 업데이트 할 수 있는 버튼을 추가할 수 있습니다.

먼저 외부 데이터 테이블(또는 임의의 장소) 위에 행을 추가합니다.여기서 파라미터 프롬프트를 빈 셀과 버튼(개발자->삽입->버튼(폼컨트롤)– 개발자 탭을 활성화해야 할 수도 있지만 그 방법은 다른 곳에서 확인할 수 있습니다.

다음으로 [External Data (blue)]영역에서 셀을 선택하고 [Data]-> [ Refresh All ( Refresh ) ](드롭다운)-> [ Connection Properties ](연결 속성...)을 열어 쿼리를 확인합니다.다음 섹션의 코드는 쿼리에 WHERE (DB_TAB) 형식의 파라미터(접속 속성 -> 정의 -> 명령어텍스트)가 이미 있는 것을 전제로 하고 있습니다.LE_NAME.Field_Name = '기본 쿼리 매개 변수'(괄호 포함).분명히 "DB_TAB"LE_NAME.Field_Name"과 "Default Query Parameter"는 데이터베이스 표 이름, 데이터베이스 값 필드(열) 이름 및 문서를 열 때 검색할 일부 기본값에 따라 코드에서 달라야 합니다(자동 새로 고침 설정)."DB_TAB"를 메모합니다.LE_NAME.Field_Name" 값은 대화상자 상단에 있는 쿼리의 "연결 이름"과 함께 다음 섹션에서 필요할 수 있습니다.

연결 속성을 닫고 Alt+F11 키를 눌러 VBA 편집기를 엽니다.아직 표시되지 않은 경우 "프로젝트" 창에서 버튼이 포함된 시트 이름을 마우스 오른쪽 버튼으로 클릭하고 "코드 보기"를 선택합니다.다음 코드를 코드 창에 붙여넣습니다(싱글/더블 따옴표는 위험하고 필요하므로 복사하는 것이 좋습니다).

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

"DB_TAB"를 바꿉니다.LE_NAME.[ Field _ Name ]및 [Connection name](2개소)에 값을 입력합니다(큰따옴표와 스페이스와 등호 포함).

"Cell reference"를 파라미터가 들어가는 셀(처음부터 빈 셀)로 바꿉니다.내 셀은 첫 번째 행의 두 번째 셀이기 때문에 "B1"을 넣었습니다(또 큰따옴표 필요).

VBA 에디터를 저장하고 닫습니다.

적절한 셀에 파라미터를 입력합니다.

버튼을 오른쪽 클릭하여 RefreshQuery 서브를 매크로로 할당하고 버튼을 클릭합니다.쿼리가 업데이트되고 올바른 데이터가 표시됩니다.

주의: 필터 파라미터 이름("DB_TAB") 전체를 사용합니다.LE_NAME.Field_Name =")는 쿼리에 조인 또는 등호 기호가 있는 경우에만 필요하며, 그렇지 않은 경우 등호만 있으면 충분하며 Len() 계산은 불필요합니다.매개 변수가 테이블 조인에도 사용되는 필드에 포함된 경우 코드에서 "paramPosition = InStr(queryPreText, valueToFilter)" + "Len(ValueToFilter) - 1" 행을 "paramPosition = InStr(오른쪽)"로 변경해야 합니다.CommandText, Len.CommandText) - InStrRev()CommandText, "WHERE", valueToFilter) + Len(valueToFilter) - 1 + InStr().CommandText, "WHERE"를 선택하면 "WHERE" 뒤에 valueToFilter만 검색됩니다.

이 답변은 datapig의 "BaconBits"를 사용하여 작성되었습니다.여기서 쿼리 업데이트의 기본 코드를 찾았습니다.

연결하려는 데이터베이스, 연결을 만든 방법 및 사용 중인 Excel 버전에 따라 달라집니다. 또한 대부분의 경우 컴퓨터에 있는 관련 ODBC 드라이버 버전입니다.

다음 예시는 모두 로컬머신에서 SQL Server 2008과 Excel 2007을 사용하고 있습니다.

데이터 연결 마법사를 사용했을 때(리본의 데이터 탭에 있는 외부 데이터 가져오기 섹션의 From Other Sources에서 수행한 것과 동일한 작업을 확인했습니다.파라미터 버튼이 비활성화되어 쿼리에 파라미터를 추가했습니다.select field from table where field2 = ?이 때문에 파라미터 값이 지정되지 않아 변경이 저장되지 않았다고 Excel이 불만을 표시하게 되었습니다.

Microsoft Query(데이터 연결 마법사와 동일한 위치)를 사용하면 매개 변수를 만들고, 매개 변수의 표시 이름을 지정하고, 쿼리를 실행할 때마다 값을 입력할 수 있습니다.해당 연결에 대한 연결 속성, 매개 변수를 불러오는 중...버튼은 활성화 되어 있고 파라미터는 원하는 대로 수정하여 사용할 수 있습니다.

액세스 데이터베이스에서도 이 작업을 수행할 수 있었습니다.Microsoft Query를 사용하여 다른 유형의 데이터베이스에 영향을 주는 파라미터화된 쿼리를 작성하는 것이 타당하다고 생각되지만, 지금은 쉽게 테스트할 수 없습니다.

언급URL : https://stackoverflow.com/questions/5434768/how-to-pass-parameters-to-query-in-sql-excel