Solved

VBA/SQL - Connect to SQL server and pull data

Posted on 2016-10-12
4
65 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
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
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
Comment Utility
it fails at line 25

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

Open in new window

0
 
LVL 13

Accepted Solution

by:
John Tsioumpris earned 500 total points
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now