nulry

http://accessjitsu.com/2015/09/1 ... an-access-database/

反而我唔知點去改 SQL d coding

Set db = OpenDatabase(DbLoc)
   
    SQL = "SELECT SalesQuarter & '-' & SalesYear, Division, GrossSales, GrossMargin "
    SQL = SQL & "FROM QuarterlySales "
    SQL = SQL & "WHERE SalesQuarter IN (1, 2) "
    SQL = SQL & "ORDER BY SalesYear, SalesQuarter "

點可以改成

SQL = SELECT A, B From "C table", where A="XXXX" and B = "NNNN" Order by C ?

edwardngc

我識SQL, 而家研究緊VBA, Are there anywhere has tutorial on running SQL in VBA? 我諗咁對我來講會易好 ...
travelntravel 發表於 2017-3-29 13:35

錄MARCO後改嘢係最易GE方法.
IN EXCEL (I'M USING 2010) DATA -> FORM OTHER SOURCES -> MS QUERY, 選DATA SOURCE, 之後寫好條SQL, 再RETURN DATA TO EXCEL. ->STOP MARCO RECORD.

你會錄倒類似以下ge東西,咁你可以晌個VBA EDITOR 度改嘢自用.


    Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=ABCDE;Description=CY142;UID=ABCDE;PWD=ABCDE;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=ABCDE;DATABASE=ABCDE;LANGUA" _
        ), Array("GE=us_english")), Destination:=Range("$A$3")).QueryTable
        .CommandText = Array("SELECT * FROM ABCDE;")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_ABCDE"
        .Refresh BackgroundQuery:=False
    End With
End Sub

nulry

回覆  nulry


SELTEXT="SELECT A, B "
FROMTEXT ="FROM C TABLE "
WHERETEXT = "WHERE A='XXX' AND B='NN ...
myricky 發表於 2017-3-31 11:33

ching, 係咪肯定咁入 ok?
有 error..

nulry

肯定係...............除非唔係...  

講笑... 我一直都係按呢類分段式varible去改sql, 會比較清 ...
myricky 發表於 2017-3-31 22:22

我發覺我只可以用 SELECT * 先出到野, 但會係成個 tablet, 我揀 columns, 會話 無值提供或多一個參數

Set Rs = .Execute(sSQL) >>> 停係呢到>..

nulry

Code 1:

Option Explicit
Sub Access_Data()
     'Requires reference to Microsoft ActiveX Data Objects xx Library
     
    Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
    Dim MyConn, sSQL As String
    Dim SelectText, FromText, WhereText, OrderText As String
    Dim Rw As Long, Col As Long, c As Long
    Dim MyField, Location As Range
     
     'Set destination
    Set Location = [A5]
     'Set source
    MyConn = "C:\Users\nulry\Documents\Access2010DB\ProfsrFinancial.accdb"
     'Create query
    SelectText = "SELECT BasicSalesQuery2 & '-' & Rep.name, "
    FromText = "FROM BasicSalesQuery2 "
    WhereText = ""
    OrderText = ""
    sSQL = SelectText + FromText + OrderText
   
    MsgBox (sSQL)
   
     'Create RecordSet
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With
     
     'Write RecordSet to results area
    Rw = Location.Row
    Col = Location.Column
    c = Col
    Do Until Rs.EOF
        For Each MyField In Rs.Fields
            Cells(Rw, c) = MyField
            c = c + 1
        Next MyField
        Rs.MoveNext
        Rw = Rw + 1
        c = Col
    Loop
    Set Location = Nothing
    Set Cn = Nothing
End Sub

nulry

'Create query
    SelectText = "SELECT Rep.name "
    FromText = "FROM BasicSalesQuery2 "
    WhereText = ""
    OrderText = ""
    sSQL = SelectText + FromText + OrderText
   
    MsgBox (sSQL)

nulry

你select *出到野即係其他部份冇問題, 只係係select field(唔係叫column)部份出錯. 咁你post select部份 ...
myricky 發表於 2017-3-31 23:08

     'Create query
    SelectText = "SELECT Rep.name "
    FromText = "FROM BasicSalesQuery2 "
    WhereText = ""
    OrderText = ""
    sSQL = SelectText + FromText + OrderText
   
    MsgBox (sSQL)