Solved

VBA/SQL - Connect to SQL server and pull data

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

734 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