Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA/SQL - Connect to SQL server and pull data

Posted on 2016-10-12
4
Medium Priority
?
212 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 18

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 18

Accepted Solution

by:
John Tsioumpris earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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