Joining tables

[Wing Chan]

Hi,

I am trying to upload data from excel to sql with the following piece of
code. However, I keep getting the syntax error on my update statement. Can
anyone give me some insights?
Thanks a million


Dim sheetname as string
Dim sqltable1 as string

sheetname = "[Upload$]"
sqltable1 = "['MSDASQL', 'DRIVER={SQL
Server};SERVER=testserver;Trusted_Connection=yes', " & db &
"..GLBUDFIL_SQL_TESTWC] as A"
sqltable = "A"

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Trim(txtFilePath.Text) & ";" & _
"Extended Properties=Excel 8.0"
cn.BeginTrans
'update first
strSQL = "UPDATE " & sqltable1 & _
" JOIN " & sheetname & _
" ON " & sqltable & ".MN_NO= " & sheetname & ".MN_NO " & _
" SET " & sqltable & ".[acct_budget_1] = " & sheetname &
".[acct_budget_1] "

cn.Execute strSQL, lngRecsUdt, adExecuteNoRecords

 

[TC]

It would be much easier to see the error if you showed us the content
of the strSQL variable.

HTH,
TC [MVP Access]

 

[Wing Chan]

Sorry TC.

Here is how the strsql variable would look like:

UPDATE ['MSDASQL', 'DRIVER={SQL
Server};SERVER=testserver;Trusted_Connection=yes',
database1..GLBUDFIL_SQL_TESTWC] as A
JOIN [Upload$]
ON A.MN_NO= B.MN_NO
SET A.[acct_budget_1] = B.[acct_budget_1]


Thanks,

Wing

 

[TC]

Wing, I don't know much about SQL*Server sql, so this is a bit of a
guess. But I notice that you have not defined 'B' as an alias name,
with an AS clause. Is that the problem?

HTH,
TC [MVP Access]