Writing VBA code from SQL Select, Update, Make Table, & Union Queries? 3

[djarman]

I have looked in many books and performed numerous internet searches on how to write queries in VBA. I have not been able to successfully manipulate any of the examples nor could I debug the program code appropriately. When I use the examples for differnt types of queries, the result is the same (unusable VBA code).

A couple of SQL code examples of the different query types in my database are pasted below.

Can someone please translate each sample SQL code into the correct VBA code. Any help will be greatly appreciated. Let me know if you need any additional information.

Select Query:

SELECT Merge.Item AS LineItem, Sum(Merge.DebitMg) AS Spent
FROM Merge
GROUP BY Merge.Item;

Update Query:

UPDATE ChargeType SET ChargeType.CStart = [Enter the Contract Start Date:], ChargeType.Cend = [Enter the Contract End Date:]
WHERE (((ChargeType.Pcode)=[Enter Program Code (Example BI4)]));

Make Table Query: It would be particularly helpful, if the Table can continue to be hidden. When I run this code in SQL, it unhides the table. I am not sure how to indicate "Hide" in the properties of the code.

SELECT [Calculate Spent Calc by Month].LineItem, [Calculate Spent Calc by Month].MthSpent, MonthName([SpentMon],True) & " " & [SpentYr] AS SpntDate INTO MthSpent
FROM [Calculate Spent Calc by Month];

Union Query:

SELECT LINE1.* FROM [LINE1] WHERE [Item] <> " "
UNION
SELECT LINE2.* FROM [LINE2] WHERE [Item] <> " "
UNION
SELECT LINE3.* FROM [LINE3] WHERE [Item] <> " "
UNION
SELECT LINE4.* FROM [LINE4] WHERE [Item] <> " "
UNION
SELECT LINE5.* FROM [LINE5] WHERE [Item] <> " "
UNION
SELECT LINE6.* FROM [LINE6] WHERE [Item] <> " "
UNION SELECT LINE7.* FROM [LINE7] WHERE [Item] <> " ";

 

[RoyVidar]

Select - I'd [bracket] Item, cause it's probably a reserwed word

[tt]strSql="SELECT [Item] AS LineItem, Sum(DebitMg) AS Spent FROM Merge GROUP BY [Item]"[/tt]

Update - don't let the users enter dates directly into stored queries! For vba sql strings, I doubt it works, but I've never bothered to find out, either - it's just not recomendable.

Then what
1 - use a form to enter the dates, then refer to the form controls directly in the sql
2 - use input boxes (ouch) and do some validation, then enter the resulting variables into the sql string
3 - use parameters (see for instance faq709-1526)

Using #1
[tt]strsql="UPDATE ChargeType SET ChargeType.CStart = #" & _
format$(me!txtContrStart,"mm\/dd\/yyyy") & _
"#, ChargeType.Cend = #" & _
format$(me!txtContrEnd,"mm\/dd\/yyyy") & _
"# WHERE ChargeType.Pcode=" & me!txtSomeExample[/tt]

For the make table hide thingie, afterwards do a

[tt]application.sethiddenattribute actable, "MthSpent",true[/tt]

For the union, are you interested in where it contains one space? Else try WHERE not [Item] is null.

When concatenating sql strings, be careful with the delimiters. Single quotes for text, hash (#) for dates, none for numerics. For dates, also consider formatting, as in the sample.

And no - I don't want to translate all your queries. There are different methods and approaches depending on how you execute this (ADO, DAO, DoCmd), what kind of server (SQL-server - single quotes as Date delimiters), and I think you'll need some try and error too;-)

Roy-Vidar

 

[FancyPrairie]

I find that the easiest way to create a SQL statement in VBA is to use Access' Query Builder. Just create your query and then switch to SQL view. Highlight the SQL statement and paste it into your VBA code and place quotation marks appropriately.

One way to test the SQL statement you created in VBA is to execute it in the Query Builder. That is, enter the statement Stop right after you create the SQL statement in VBA. Then, via debug's Immediate window, examine the variable that holds the SQL statement, then highlight it, copy it, and create a new query, switch to SQL view and paste the SQL statement. Now run it to verify that it is doing what you want.

Example:

Dim strSQL as String

strSQL = "Select * from YourTable;"
Stop

When your code hits the stop statement, it will pause execution. In Debug's Immediate window type ?strSQL. To continue execution until the end or the next breakpoint (or stop statement) press F5. To continue execution one line at a time, press F8. To set a breakpoint on a line, press F9.

 

[djarman]

Thanks for your responses, they have been very helpful. My next related challengs is as follows:

Seven tables (called Line1 - 7) were created from the same DB, all having the same structure. I have been able to merge the tables (into table "Merge") together with SQL by performing a union query then a make table query. Is there solution in VBA?

I have pasted the SQL code below. I plan to use the DoCMD.RunSQL route, but would like to know if there are other options.

SQL
SELECT [LINE1.*] FROM [LINE1] WHERE [Item] <> " "
UNION
SELECT [LINE2.*] FROM [LINE2] WHERE [Item] <> " "
UNION
SELECT [LINE3.*] FROM [LINE3] WHERE [Item] <> " "
UNION
SELECT [LINE4.*] FROM [LINE4] WHERE [Item] <> " "
UNION
SELECT [LINE5.*] FROM [LINE5] WHERE [Item] <> " "
UNION
SELECT [LINE6.*] FROM [LINE6] WHERE [Item] <> " "
UNION SELECT [LINE7.*] FROM [LINE7] WHERE [Item] <> " ";

SELECT Query8.*, -[Credit] AS Credit2, IIf([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS DebitMg INTO Merge
FROM Query8;

Two VBA codes that I thought would work but hasn't

Private Sub Append_Tables()

Dim TargetTable As String
Dim tbl As tdd 'TableDef

TargetTable = "Merge2"
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 7) = "OUTFUT" Then
SourceTable = tbl.Name
StrSql = "INSERT INTO [" & TargetTable & "]"
StrSql = StrSql & " SELECT ' & [Line1] & '.*"
StrSql = StrSql & " SELECT ' & [Line2] & '.*"
StrSql = StrSql & " SELECT ' & [Line3] & '.*"
StrSql = StrSql & " SELECT ' & [Line4] & '.*"
StrSql = StrSql & " SELECT ' & [Line5] & '.*"
StrSql = StrSql & " SELECT ' & [Line6] & '.*"
StrSql = StrSql & " SELECT ' & [Line7] & '.*"
StrSql = StrSql & " FROM " & SourceTable
DoCmd.SetWarnings False
DoCmd.runSQL StrSql
DoCmd.SetWarnings True
End If
Next
End Sub

OR

Dim strSql8 As String
Dim strSql9 As String

strSql8 = "SELECT [LINE1.*] FROM [LINE1] WHERE [Item]<> ' ' UNION " & _
"SELECT [LINE2.*] FROM [LINE2] WHERE [Item] <> ' ' UNION " & _
"SELECT [LINE3.*] FROM [LINE3] WHERE [Item] <> ' ' UNION " & _
"SELECT [LINE4.*] FROM [LINE4] WHERE [Item] <> ' ' UNION " & _
"SELECT [LINE5.*] FROM [LINE5] WHERE [Item] <> ' ' UNION " & _
"SELECT [LINE6.*] FROM [LINE6] WHERE [Item] <> ' '" & _
"UNION SELECT LINE7.* FROM [LINE7] WHERE [Item] <> ' ';"

'strSql9 = "SELECT Query8.*, -[Credit] AS [Credit2], IIf([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg] INTO [Merge] FROM [Query8];"

'Turn off warnings
DoCmd.SetWarnings False
DoCmd.runSQL strSql8
DoCmd.runSQL strSql9

-------------------------------------------
I spent probably 5 hours trying variants of the code above to get them to work. Any help will be greatly appreciated.

 

[hkaing79]

Perhaps this code:

Dim i as integer

for i = 1 to 7
  currentdb.execute "SELECT *, -[Credit] AS [Credit2], IIf([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg] INTO [Merge] FROM Line" & i
next i

 

[hkaing79]

Scratch that. I should've tested the code first. Sorry.

It's having problems inserting into an existing table.

 

[hkaing79]

Try this code:

Dim i as integer

currentdb.execute "SELECT *, -[Credit] AS [Credit2], IIf([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg] INTO [Merge] FROM Line1"

for i = 2 to 7
  currentdb.execute "INSERT INTO Merge SELECT *, -[Credit] AS [Credit2], IIf([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg] INTO [Merge] FROM Line" & i
next i

 

[djarman]

Thanks,

This is the code that worked. Thanks for everyone's help.

strSQL7 = "SELECT [Vouchers].[VoucherNum], [Vouchers].[BatchNUm], " & _
"[Vouchers].[VendorNum], [Vouchers].[Payto], [Vouchers].[Address]," & _
"[Vouchers].[Address2], [Vouchers].[City], [Vouchers].[State]," & _
"[Vouchers].[Zip], [Vouchers].[PCode], [Vouchers].[CheckNum], " & _
"[Vouchers].[CheckDate], [Vouchers].[Initials], " & _
"[Vouchers].[InvoiceNum], [Vouchers].[InvoiceDate], [Vouchers].[Date], " & _
"[Vouchers].[ClearDate], [Vouchers].[Description], " & _
"[Vouchers].[Person], [Vouchers].[Total], [Vouchers].[Paid], " & _
"[Vouchers].[Item7] AS [Item], [Vouchers].[Debit7] AS [Debit], " & _
"[Vouchers].[Credit7] AS [Credit], " & _
"[Vouchers].[Total Check], [Vouchers].[Comments] " & _
"INTO [Line7] " & _
"FROM [Vouchers] " & _
"WHERE (((Vouchers.Item7)<>' '));"

strSQL8 = "SELECT *, -[Credit] AS [Credit2], IIf([Debit]>0," & _
"[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg] INTO [M] FROM [Line1];"


'Insert Line2 - Line7 information into M.
For i = 2 To 7
CurrentDb.Execute "INSERT INTO [M] SELECT *, -[Credit] AS [Credit2], IIf" & _
"([Debit]>0,[Debit],IIf([Credit2]<0,[Credit2],0)) AS [DebitMg]" & _
"FROM Line" & i
Next i