Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Go Back  Xtreme Visual Basic Talk > > > > Convert SQL queries to generic statements in VBA


Reply
 
Thread Tools Display Modes
  #1  
Old 11-26-2015, 06:35 PM
dd8 dd8 is offline
Newcomer
 
Join Date: Jul 2015
Posts: 3
Default Convert SQL queries to generic statements in VBA


Running/writing a VBA application containing sql. Im unfamiliar with SQL

Ive converted the sql queries to VBA and the code successfully returns data queried.

Currently each sql statement is hard-coded. I would like to create a generic function that executes a sql statement. This means string variables would replace the hard-coded sql. However I don’t know how to write the correct sql statement syntax for it to work.

This is the original code that works – each sql is hardcoded


Code:
Sub GetSQLData()

Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim ConnectionString As String


Set Conn = New ADODB.Connection

ConnectionString = "connect to db here"

Conn.ConnectionString = ConnectionString
Conn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient


'' matter name
'rs.Open "SELECT Matter_Name FROM STAGE_Matter WHERE MATTER_CODE = '9019817' AND ND_Sync_Status = 'SYNCED'", Conn


'' client name
'rs.Open "SELECT CLIENT_NAME FROM STAGE_Client WHERE CLIENT_CODE = 'TEST10884' AND ND_Sync_Status = 'SYNCED'", Conn


'' author fullname
'rs.Open "SELECT FullName FROM STAGE_Person WHERE ShortName = 'DDAVIDSON'", Conn


If rs.RecordCount >= 1 Then
    MsgBox rs.RecordCount
    MsgBox rs.Fields(0).Value
    
End If

End Sub
This was the amended coded provided to me to pass a variable. This works

Code:
Dim strData As String

strData = "9019817"
rs.Open Replace("SELECT Matter_Name FROM STAGE_Matter WHERE MATTER_CODE = '%VALUE%' AND ND_Sync_Status = 'SYNCED'", "%VALUE%", strData), Conn


'strData = "TEST10884"
'rs.Open Replace("SELECT CLIENT_NAME FROM STAGE_Client WHERE CLIENT_CODE = '%VALUE%' AND ND_Sync_Status = 'SYNCED'", "%VALUE%", strData), Conn


'strData = "DDAVIDSON"
'rs.Open Replace("SELECT FullName FROM STAGE_Person WHERE ShortName = '%VALUE%'", "%VALUE%", strData), Conn
However I would prefer more generic sql statements, something along the lines of …

Code:
Dim strData As String
Dim strField as String
Dim strSearchField as String
Dim strTable as String
Dim strSql as String


strSql = "SELECT strField " & vbCrLf & _
"FROM strTable " & vbCrLf & _
"WHERE strSearchField = strData AND ND_Sync_Status = 'SYNCED'",Conn
Do you know how I can do this, or have samples

TIA

--dd
Reply With Quote
  #2  
Old 11-26-2015, 10:34 PM
Kluz's Avatar
KluzConvert SQL queries to generic statements in VBA Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You are very close to the solution. The & vbCrLf & part is unnecessary as is the use of the Replace Function.
Code:
strField = "Matter_Name" strTable = "STAGE_Matter" strSearchField = "MATTER_CODE" strData = "9019817" rs.Open "SELECT " & strField & " FROM " & strTable & _ " WHERE " & strSearchField & " = '" & strData & "' AND ND_Sync_Status = 'SYNCED'", Conn
You will need to pay close attention to the use of the space character before and after the concatenation of the string variables.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
sql, dim, strdata, conn, string, %value%, rs.open, nd_sync_status, select, synced, statement, connectionstring, fullname, generic, vba, replaceselect, set, strtable, strsearchfield, strsql, adodb.recordset, stage_person, vbcrlf, adodb.connection, matter_name


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
 
Convert SQL queries to generic statements in VBA
Convert SQL queries to generic statements in VBA
 
-->