Solved

Problem to run against Sql server DB

Posted on 2015-01-02
10
143 Views
Last Modified: 2015-01-07
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
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 40527333
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
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40527335
Thanks. what to adjust in above?
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40527336
Can you please show with more details? thanks.
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527339
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40527349
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
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40527360
Many thanks all. Can I know exactly what I should adjust, to my codes in above?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527368
We don't know your exact connection requirements and strings, so no.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40527372
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 40527620
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 250 total points
ID: 40527646
... and you are on the same domain (because of Trusted Connection / Integrated Security) - yes.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question