Solved

VBA/SQL - Connect to SQL server and pull data

Posted on 2016-10-12
4
119 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 15

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 15

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

820 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