Executing SQl query using VBA (1 Viewer)

olivia.weaver

New member
Local time
Today, 14:23
Joined
Nov 26, 2019
Messages
5
I have created a VBA code which generates a userform/textbox with "INSERT INTO ..." statements that I then have to copy and paste into SQL to run. I want to create a button on my userform which: a) automatically runs the query in SQL by referencing the text inside the userform and executing it, and b) returns how many rows were inserted

Note: I can create an ADODB.Connection and execute SQL queries that way but the code I use to create my INSERT INTO statement is quite complex and results in a string. I believe you cannot use an "Execute" function with a string.
 

June7

AWF VIP
Local time
Today, 06:23
Joined
Mar 9, 2014
Messages
5,425
Re: Executing SQL query using VBA

VBA certainly can run an Execute on SQL string.

CurrentDb.Execute "INSERT INTO …"

But maybe I am not understanding the issue. Why would you need ADODB connection?
 

olivia.weaver

New member
Local time
Today, 14:23
Joined
Nov 26, 2019
Messages
5
Re: Executing SQL query using VBA

When I run: conn.Execute finalQuery, where finalQuery references the string I get the following error:

"Run-time error '-2147217908' Command text was not set for the command object."
 

June7

AWF VIP
Local time
Today, 06:23
Joined
Mar 9, 2014
Messages
5,425
Need to post procedure for analysis, not bits of code out of context.

Also, answer to question would be nice.

What is structure of db? Split? What is used as backend?
 

isladogs

MVP / VIP
Local time
Today, 14:23
Joined
Jan 14, 2017
Messages
18,186
No need to use ADODB.

You can run code similar to this

Code:
Dim strSQL As String
StrSQL="INSERT INTO ……"
CurrentDb.Execute strSQL
 

olivia.weaver

New member
Local time
Today, 14:23
Joined
Nov 26, 2019
Messages
5
Apologies, first time using this. I believe I need the connection to link VBA to the server used for SQL.

Here's the code I am using currently which generates a userform/textbox where I manually copy and paste the code into SQL and run the query.


Code:
Sub UploadSQL()

Application.ScreenUpdating = False


Dim mainPortFieldRow As Integer
Dim mainPortFieldList As String
Dim firstCol As Integer
' Find some rows/cells

mainPortFieldRow = Range("MainPortfolioFieldsRow").Row

firstCol = Range("PortfolioStartCell").Column

' Build field lists

mainPortFieldList = BuildFieldList(mainPortFieldRow, firstCol)


Dim bId As Integer
Dim splitBits As Variant
Dim stringArray() As String

splitBits = Split(Range("bID_Uploader"), ":")
bId = splitBits(0)

' Build full SQL query

Dim finalQuery As String
Dim rowCursor, colCursor As Integer
Dim firstField As Boolean
Dim index As Integer

' Determine required array size

rowCursor = Range("PortfolioStartCell").Row
index = 1

Do While Cells(rowCursor, firstCol) <> ""
    index = index + 1
    rowCursor = rowCursor + 1
Loop



    ReDim stringArray(index + 100)

    ' Main Portfolio

    rowCursor = Range("PortfolioStartCell").Row
    index = 0




        Do While Cells(rowCursor, firstCol) <> ""

        If Cells(rowCursor, 12) <> "" Then

            finalQuery = finalQuery & "INSERT INTO Table " & mainPortFieldList & ") VALUES (" & bId & ""

            colCursor = firstCol


            Do While Cells(mainPortFieldRow, colCursor) <> ""

                If Cells(mainPortFieldRow, colCursor) <> "<ignore>" Then

                    finalQuery = finalQuery & "," & ProcessFieldValue(Cells(mainPortFieldRow, colCursor), Cells(rowCursor, colCursor))

                 firstField = False
              End If


                colCursor = colCursor + 1

            Loop


            finalQuery = finalQuery & ") "
            End If


            rowCursor = rowCursor + 1
            stringArray(index) = finalQuery
            finalQuery = ""
            index = index + 1

          If (rowCursor Mod 1000) = 0 Then
'              MsgBox rowCursor
          End If


    Loop




' Execute the query!

    Application.ScreenUpdating = True

    finalQuery = Join(stringArray, vbCrLf)
    SQLQueryForm.sqlQueryBox.Text = finalQuery
    SQLQueryForm.Show

   'Call DBQuery(finalQuery)



End Sub


I'd like to have an insert button which does this without me having to manually copy and paste the result into SQL.
 
Last edited:

olivia.weaver

New member
Local time
Today, 14:23
Joined
Nov 26, 2019
Messages
5
Appreciate your help IslaDogs but grateful if you could link it to my code above I have posted. I'm not sure how I would reference my "finalQuery" within your code.
 

June7

AWF VIP
Local time
Today, 06:23
Joined
Mar 9, 2014
Messages
5,425
I am confused. Code looks like Excel VBA.
 

olivia.weaver

New member
Local time
Today, 14:23
Joined
Nov 26, 2019
Messages
5
Yes - it's VBA code. It produces the query in a textbox which I input into SQL. However I want a button on my excel which automatically runs the code produced in SQL without having to manually copy and paste.
 

Users who are viewing this thread

Top Bottom