nulry

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

ching, 唔該哂，　知道咩問題，係 table 入面,  最頂個行, 叫 A, 但原來係最 raw 既 data 係叫 aB, 所以 call 唔到.~~

nulry

SelectText = "SELECT Rep.name "
    FromText = "FROM BasicSalesQuery2 "
    WhereText = ""
    Or ...
myricky 發表於 2017-4-1 00:36

會唔會係BasicSalesQuery2係一個query table, 而Rep係data table? 而係只係想要Rep table入面'name'呢個field?

係呢個，　所以唔得～～　唔好意思，　唔太熟 SQL

thx~~

nulry

SelectText = "SELECT Rep.name "
    FromText = "FROM BasicSalesQuery2 "
    WhereText = ""
    Or ...
myricky 發表於 2017-4-1 00:36

ching 呢家攞 data 既 VBA 搞好左, 咁要 feed 去 database 呢?

我睇番我公司, manger 寫個 d 太覆雜......睇唔明, 你有無 d coding 較 easy?

nulry

揾到 export Excel data to database coding

Option Explicit
Sub ADOXLtoSQLSRV()
    Dim cn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String
    Dim strXLSource As String
    Dim lngRecsAff As Long
    strConn = strConn & "Provider=SQLOLEDB;Data Source=BIG-TOSH;"
    strConn = strConn & "Initial Catolog=Customers;Trusted_Connection=YES"
    Set cn = New ADODB.Connection
    cn.Open strConn
    strXLSource = "C:\\AccountNos.xls;Extended Properties=Excel 12.0"
    strSQL = " INSERT INTO Customers.dbo.XLImport "
    strSQL = strSQL & " ([Account]) "
    strSQL = strSQL & " SELECT [Account] "
    strSQL = strSQL & " FROM "
    strSQL = strSQL & " OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=" & strXLSource & "')...[tblAccounts$] "
     
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
     
    cn.Close
     
End Sub
-------------------------
但好似見唔到由 sheet 既邊個 range 開始....

有無師兄有 ideas/

孤寂使者

VBA + SQ點樣易入門呀?有速成大法

nulry

Hi all~~

試到了，　有 隻簡單 d 既 coding

Public Sub DoTrans2()

Dim i, j, m, n As Integer
Dim sh1 As Worksheet
Dim db(1 To 1000, 1 To 1000) As Variant
Dim dbWb, dbWs As String

Set sh1 = ThisWorkbook.ActiveSheet

Set Cn = CreateObject("ADODB.Connection")

dbPath = sh1.Cells(3, 2)

dbWb = Application.ActiveWorkbook.FullName

dbWs = Application.ActiveSheet.Name

scn = "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=" & dbPath

dsh = "[" & Application.ActiveSheet.Name & "$]"

Cn.Open scn

For i = 7 To 8

sSQL = "INSERT INTO abc Values ('" & Cells(i, 1) & "' ," & Cells(i, 2) & " ) "

Cn.Execute sSQL

Next


End Sub

nulry

但有個問題係，　我唔識熟點去 create database

我 feed 去 ms access 個時, 佢會話我個 key index dupciate, 因為我其求揾 d data 去試, e.g. abc, 123...

咁係咪我最好用 date 去做一個 key index, 咁其他 filed 就算重覆左都無問題?