Excel VBA code to connect to SQL and show data on new Work sheet

[jaime72]

Hi I am trying to create an excel speadsheet that will ask for a job number to be entered before retrieving data such as Job address, job type etc for that job and populate the page like a job sheet.. I am using Mariadb on a Synology nas to store the data.. I have read several threads here and used an example from this awesome site, but it gives me 'runtime error 91. Object variable or With block variable not set'
and this is the code it stops on:

Set rs = gosql.Execute(strsql)

I am guessing that even though it seems to connect successfully, maybe this line is the problem:

strsql = "SELECT * FROM `evo`.`projects` "

As i think it returns no data??
I haven't worked out the rest of he code yet as i am new to this. here is the full code so far:

Option Explicit
Public gosql As Object
Public Const gblcstrServer As String = "192.168.1.59"


Public Const gblcststrLoadCatalog As String = "evoplans"
Public Const gblcststrID As String = "root"
Public Const gblcststrPWD As String = "pxxxxxxxx8"

then this

Private gosql As Object
'
'first create a function that defines the SQL login credentials so that everything can be automated
'
Public Function Connect() As Boolean
Dim lsConnStr As String
If gosql Is Nothing Then
    lsConnStr = "Driver={MySQL ODBC 5.3 Unicode Driver};" & _
                "Uid=" & gblcststrID & ";" & _
                "Pwd=" & gblcststrPWD & ";" & _
                "Server=" & gblcstrServer & ";" & _
                "Database=" & gblcststrLoadCatalog
'
'if any of the credentials are incorrect, jump to error trapping code
'
    On Error GoTo ConnectFailed
    Set gosql = CreateObject("ADODB.Connection")
    gosql.ConnectionTimeout = 5
    gosql.Open lsConnStr
End If
'
'connect successful
'
Connect = True
Exit Function
'
'error trap
'
ConnectFailed:
    MsgBox "wo Could not connect to server" & Chr(13) & Err.Description
    Set gosql = Nothing
    Connect = False
End Function

the next bit is the problem

       Sub foo()
        If Not Connect Then Exit Sub
Dim strsql As Variant
irow = 5
'
'get year and period from spreadsheet
'
'yearflag = Sheets("TB").Cells(2, 2)
'periodno = Sheets("TB").Cells(1, 2)
'
'SQL code
'
strsql = "SELECT * FROM `evo`.`projects` "
'
'prepare sheet
'
Sheets("Sheet1").Cells(4, 1) = "Code"
Cells(4, 1).Font.Bold = True

Columns("C:D").Select
Selection.NumberFormat = "0.00"
Range("C1").Select
Application.ScreenUpdating = False
'
'execute script
'
Set rs = gosql.Execute(strsql) 'i get error here
While Not (rs.EOF Or rs.BOF)
'
'populate sheet
'
Sheets("TB").Cells(irow, 1) = rs("NCODE")
Sheets("TB").Cells(irow, 2) = rs("NNAME")
Sheets("TB").Cells(irow, 3) = rs("DEBIT") - rs("CREDIT")
irow = irow + 1
rs.movenext
Wend
'
'while loop terminates
'
Application.ScreenUpdating = True
rs.Close
Set rs = Nothing
End Sub

I realise the end sheet(tb) part doesn't relate to the rest of my code but i just want data returned first before i try to fix the rest.
Please any excel wizards out there that can help me??
Please excuse sloppiness of my first post.

 

[jaime72]

Okay I worked it out finally using DSN to get data

Private Sub CommandButton1_Click()
Dim myValue As Variant
myValue = InputBox("Enter Job Number")
Range("B2").Value = myValue
End Sub

Private Sub CommandButton2_Click()

    'Declare a Connection object
    Dim cnDB As New ADODB.Connection

    'Declare a Recordset Object
    Dim rsRecords As New ADODB.Recordset

    'Open the ODBC Connection using this statement
    cnDB.Open "evoplans"
    rsRecords.Open "Select * from evo_projects , evo_tasks , evo_companies where project_id =  " & Range("B2").Value, cnDB

    'Print the numberof records in A1 cell
    Sheet1.Range("B4") = rsRecords("project_name")
    Sheet1.Range("B6") = rsRecords("project_description")
    Sheet1.Range("B9") = rsRecords("task_name")
    Sheet1.Range("B10") = rsRecords("company_name")



    'Close everything and set the references to nothing
    rsRecords.Close
    Set rsRecords = Nothing
    cnDB.Close
    Set cnDB = Nothing
End Sub

But now my problem is displaying data from different tables within the database, for example
In the evoplans database is a table called evoprojects which has columns project_id (job number) & project_name & Project_company.. my problem is the value in project_company displayed is a number that is the company_id , which the value of is stored in another table called evo_companies but i need company_name instead?

How do i adjust the code above to retrieve the company name based on the company_id?
thanks in advance

 

[jaime72]

to explain further when i use:

Sheet1.Range("B10") = rsRecords("company_name")

i get the first company name from evo companies column not the company linked to the job_id
if i use

Sheet1.Range("B10") = rsRecords("project_company")

i get the company id eg. 2 instead of the company name linked to that id