Problem to run against Sql server DB

Hi,
I actually want to connect to Sql server DB, from Excel VBA codes, using these

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CBRHKMA;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    ...
            s = "delete from [ODBC;Description=TEST;DRIVER=SQL Server;" _
            & "SERVER=PC336_W7_HKMA;Trusted_Connection=Yes;" _
            & "DATABASE=CBRHKMA].T41 " _
            & "where [Reporting Date]=convert(datetime,'" & Mid(strLine, pos0, 8) & "',112)"
            ...

Open in new window

but I get the error attached. why?
t917.png
LVL 11
HuaMinChenSystem managerAsked:
Who is Participating?
 
QlemoConnect With a Mentor DeveloperCommented:
... and you are on the same domain (because of Trusted Connection / Integrated Security) - yes.
0
 
QlemoDeveloperCommented:
When using Jet drivers you need to provide aa valid file as source. You should use a direct ODBC connection without that Jet stuff.
0
 
HuaMinChenSystem managerAuthor Commented:
Thanks. what to adjust in above?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HuaMinChenSystem managerAuthor Commented:
Can you please show with more details? thanks.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The reason you are getting this error is because of

Data Source=CBRHKMA

Because you haven't got a folder path, it is interpreting this as CBRHKMA.xls in the current directory.

For more information around connecting to SQL Server from Excel, see http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28509282.html

If you want some straight-forward code, go to http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm and "Show source code" of "SQL Server Data Import to Excel using ADO".
0
 
Pratima PharandeCommented:
ResolutionTo work around this issue, carefully verify not only the name and spelling of the source table (or tables) named in your query, but the name and spelling of the Excel path and file name supplied in your ADO connection string.

refer

http://support.microsoft.com/kb/316809/en-us
0
 
HuaMinChenSystem managerAuthor Commented:
Many thanks all. Can I know exactly what I should adjust, to my codes in above?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
We don't know your exact connection requirements and strings, so no.
0
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
0
 
HuaMinChenSystem managerAuthor Commented:
Thanks all.
will it be fine, if I have the codes like

    Dim cn As ADODB.Connection
    Set cn = CreateObject("ADODB.Connection")
    
    'strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CBRHKMA;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    strCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=PC336_W7_HKMA;" & _
                              "InitialCatalog=CBRHKMA;"
    
    cn.Open strCon
    ...
            s = "delete from T41 " _
            & "where [Reporting Date]='" & date_part & "'"
            
            cn.Execute s
            ...

Open in new window


supposing that there is no syntax error above, and PC336_W7_HKMA is server name, and CBRHKMA is schema name?
0
All Courses

From novice to tech pro — start learning today.