Solved

VBA/SQL - Connect to SQL server and pull data

Posted on 2016-10-12
4
187 Views
Last Modified: 2016-10-12
Hi guys, I have a working solution with MS Access but need to shift it to a MS SQL Server now, and for that i need to ammend the code.... I have tried but no luck... please advise

What the code does is basically a button in the ribbon that checks the which printer the user has installed and connects to the database and find the printer on the list and then set the tray to either 1 or 2 depend on what is defined in the collumn 'Brevpapir'

Please advise, it seems like it do connect to the SQL server but fails when it should pull data from it

This is the Code

Sub paperbin()
    Dim objDoc As Word.Document
    Dim conn As New Connection
    Dim strCon As String
    Dim strQry As String
    Dim rs As New Recordset
    Dim strShareName As String
    Dim intPoz As Integer
    Dim strUncName As String
    
    On Error GoTo Error
    
    strUncName = DefaultPrinter
    intPoz = InStrRev(Left(strUncName, Len(strUncName) - 2), "\") + 1
    strShareName = Mid(strUncName, intPoz, Len(strUncName) - intPoz + 1)
    Set objDoc = ActiveDocument
    'strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\bfd2\b00037\Centraldata\CDI-DK\Printbakkevalg\printbakke.accdb;"
    strCon = "Provider=MSDASQL.1;driver={SQL Server};Server=XXXXXXXX;Database=Printknap;User Id=XXXXXX;Password=XXXXXX;"
    
    conn.Open (strCon)

    'strQry = "SELECT brevpapir, normal FROM HBprintere WHERE (((HBprintere.[Share Name])=""" & strShareName & """));"
    strQry = "SELECT Brevpapir, Normal FROM dbo.HBprintere WHERE (((dbo.HBprintere.ShareName)=""" & strShareName & """));"
    rs.Open strQry, conn, adOpenKeyset
    If rs.BOF = False And rs.EOF = False Then <==== IT FAILS HERE!!
        With objDoc.PageSetup
            .FirstPageTray = rs.Fields(0)
            .OtherPagesTray = rs.Fields(1)
            .SectionStart = wdSectionNewPage
        End With
        Application.PrintOut Range:=wdPrintRangeOfPages, Pages:="1-1", copies:=frmPrint.txtKopier.Value
        With objDoc.PageSetup
            .FirstPageTray = rs.Fields(1)
            .OtherPagesTray = rs.Fields(1)
            .SectionStart = wdSectionNewPage
        End With
        Application.PrintOut Range:=wdPrintRangeOfPages, Pages:="2-", copies:=frmPrint.txtKopier.Value
    End If
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    Exit Sub
Error:
    Call MsgBox("Der er problemer med print bakkevalg." _
                & vbCrLf & "Fortsætter problemet kontakt system administrator." _
                , vbExclamation Or vbSystemModal, "Udskriv")
End Sub

Open in new window

0
Comment
Question by:Hakum
[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
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:John Tsioumpris
ID: 41839659
At what exact point it fails...
If it fails when
 conn.Open (strCon)

Open in new window

then you have some issue with the Connection string...
If it fails later then you have an issue with your query...
0
 
LVL 1

Author Comment

by:Hakum
ID: 41839662
it fails at line 25

   
 If rs.BOF = False And rs.EOF = False Then <==== IT FAILS HERE!!

Open in new window

0
 
LVL 17

Accepted Solution

by:
John Tsioumpris earned 500 total points
ID: 41839665
The quick and dirty solution to check it is to  it make a DSN to your SQL and make a passthrough query with your query...
I guess something is wrong with it and doesn't retrieve any data
Edit i found out what the issue is...you are using double quotes
Your query should be :
strQry = "SELECT Brevpapir, Normal FROM dbo.HBprintere WHERE (((dbo.HBprintere.ShareName)='" & strShareName & """)) &"'"

Open in new window

0
 
LVL 1

Author Closing Comment

by:Hakum
ID: 41839798
I just found it myself aswell!!

Thank you soooo much!!! you rock!!

strQry = "SELECT Brevpapir, Normal FROM dbo.HBprintere WHERE ShareName='" & strShareName & "';"
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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