Underlake Reading Room

这里是图书馆外的阅览室🌟

0%

关于SQL的笔记 - 和VBA新春混合喜庆版

Posted on Edited on In Disqus: 8k 7 mins.

2021错题本.

这个系列两年后卷土重来了…
最近在用ms access, access中也可以使用sql,但跟其他sql语法不大一样(低情商:简陋).
于是记录access中的sql使用, 亦或是 用vba绕过sql的使用.





Notes

以下最常见在vba中的sql使用.

OpenRecordSet

1
2
3
4
5
Dim sSQL As String
Dim rs As DAO.Recordset

sSQL = "SELECT First(Apple) FROM tableA;"
Set rs = CurrentDb.OpenRecordSet(sSQL)

如果不需要select的情况可以直接开table.

1
2
3
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordSet("The_Table_Name", dbOpenTable)
1
2
3
Dim Oapple As Object

Set Oapple = rs.Fields("Apple").Value

这里table的value可以有很多种,table中甚至可以store文件.

OpenRecordSet之后如何使用其value: 使用了getRows.



注意,

1
2
3
Dim theName As String

Set Oapple = rs.Fields(theName).Value

可以这样用variable.

1
Set Oapple = rs.Fields!theName.Value

这时候theName必须生死rs中已有的column的名字,不可以用variable.

Using Variable on accessing the Form and Controls in MS Access.
Form中也类似.

1
2
3
4
5
Set theFields As String
theFields = "myFieldName"

Forms(“theFormName”).Controls(theFields).Locked = False
Forms(“theFormName”).Controls(theFields).Enabled = True

这样用.



Execute

不需要store 信息时候使用db,也就是execute.
比如update, delete的function.

1
2
3
4
5
6
Dim sSQL As String

sSQL = "UPDATE tableA " & _
      "SET fruits = ""None"", time = Now() " & _
      "WHERE label = ""apple"";"
CurrentDb.Execute sSQL
1
2
3
4
5
6
7
8
Dim db As DAO.Database
Dim sSQL As String

Set db = OpenDatabase("MoreFruits.accdb")
sSQL = "UPDATE tableA " & _
      "SET fruits = ""None"", time = Now() " & _
      "WHERE label = ""apple"";"
db.Execute sSQL


Function

How to return a result from a VBA function.

VBA有两种function,一种key word 是sub,一种key word是function.



Listbox

这是access form中的信息display.

1
2
3
4
5
6
7
8
sSQL = "SELECT fruits FROM tableA;"
'Display is listbox
Me.Display.RowSource = sSQL
Me.Display.Requery

'at the end of the form
Forms!MyFruitForm.Refresh
Forms!MyFruitForm.Requery

ComboBox.RowSource property.



vbCrLf

Differences Between vbLf, vbCrLf & vbCr Constants.

Constant Value Description
vbCr Chr(13) Carriage return(返回到行首)
vbCrLf Chr(13) & Chr(10) Carriage return–linefeed combination(类似于按回车键)
vbLf Chr(10) Line feed(转到下一行)


Cases

SQL join against date ranges

Join two tables on the same date or closest date

这可真是个好问题,我觉得stack over flow的答案写的很好.
但在access 里面实现太麻烦了…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--先用order和self join作出一个end_date的column
WITH IndexedExchangeRates AS (           
            SELECT  Row_Number() OVER (ORDER BY Date) ix,
                    Date,
                    Rate
            FROM    ExchangeRates
        ),
        RangedExchangeRates AS (             
            SELECT  CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime)
                    ELSE IER.Date
                    END DateFrom,
                    COALESCE(IER2.Date, GETDATE()) DateTo,
                    IER.Rate
            FROM    IndexedExchangeRates IER
            LEFT JOIN IndexedExchangeRates IER2
            ON IER.ix = IER2.ix-1
        )
SELECT  T.Date,
        T.Amount,
        RER.Rate,
        T.Amount/RER.Rate ConvertedAmount
FROM    Transactions T
LEFT JOIN RangedExchangeRates RER
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)

--然后再comparison join


Form load

Form load中的代码会over write在design view中各compent的default value.
Form的跳转的觉得下面这个link说的有道理.

How to call Form_Load of one form from another form in Access VBA

做一个public的function然后call public就好了.Form load有的时候没办法handle从哪个form跳转而来的问题.



Add index

1
ALTER TABLE fruits ADD COLUMN ID COUNTER;


On Error Resume Next

才直到这个是一个switch,这行代码会让之后出现的所有error都被跳过.
超级危险,不要用.



Export data

TableName:Variant A string expression that is the name of the Office Access table that you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query whose results you want to export to a spreadsheet.

用query来export.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim MyQuery As DAO.QueryDef
Dim sSQL As String
Dim WorksheetPath As String

WorksheetPath = "C:/SomethingHere"
'先造了一个query出来

sSQL = "SELECT * From DoYouStillHaveFruit"
Set MyQuery = CurrentDb.CreateQueryDef("NewQuery", sSQL)

DoCmd.TransferSpreadsheet transfertype:acExport,
  Spreadsheettype:=acSpreadsheetTypeExcel12Xml,
  TableName:="NewQuery",
  FileName:=WorksheetPath & "theRobbingList.xlsx",
  Hasfieldnames:= True

我觉得这个地方documentation写的“query也行”不是SQL string,而是真的得是define好的query.
比如在access页面也能create query.
用define好的query名字也行.

Create后的delete.



用access中的table来export.

1
2
3
4
5
6
7
8
9
10
11
Dim MyQuery As DAO.QueryDef
Dim theTable As String
Dim WorksheetPath As String

theTable = "theAccessTable"
WorksheetPath = "C:/SomethingHere"
DoCom.TransferSpreadsheet transfertype:acExport,
  spreadsheettype:=acSpreadsheetTypeExcel12Xml,
  TableName:=theTable,
  FileName:=WorkSheetPath & "theRobbingList.xlsx",
  Hasfieldnames:=True

关于如何用代码来修改table内容.
可以用OpenRecordset.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim myInput, myOutput As DAO.Recordset
Set myInput = CurrentDb.OpenRecordset("SELECT * FROM theTable")

'EOF应该是end of file
'代表跑完rst所选中的所有记录
Do While Not myInput.EOF
  DoEvents
  myOutput.AddNew
  myInput![theColumnName] = myOutput![theOtherColumnName]
  myOutput.Updaate
  myInput.MoveNext
Loop

myInput.Close
myOutput.Close

这里的DoEvents是用来让break button active的(据说).

上面使用了EOF,microsoft给的例子是:

1
If myInput.EOF = True Or myInput.BOF = True Then

我觉得这样写好(?).



Default value

(For excel as well)
Data is stored as number, so when setting default values for date can use number format instead of any long/short data format cuz somethings I have no idea why it’s not working(怒).

Btw for excel when a sheet is hidden… and the tab is locked for unhidden sheet, use alt+F11 to go to VBA module, can find the sheet name, then go back to sheet using reference to display the value anyway(暴怒).



Delete table

DoCmd.DeleteObject method.

1
DoCmd.DeleteObject acTable, "Former Employees Table"

Delete if exist.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Public Sub DeleteIfExists()

    Dim tableName As String
    tableName = "Table3"

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        Debug.Print "Table" & tableName & "deleted..."
        DoCmd.SetWarnings True
    End If

End Sub


Open file

根据Application.FileDialog property.

Name Value Description
msoFileDialogFilePicker 3 File Picker dialog box.
msoFileDialogFolderPicker 4 Folder Picker dialog box.
msoFileDialogOpen 1 Open dialog box.
msoFileDialogSaveAs 2 Save As dialog box.

所以选中文件是f = Application.FileDialog(3).

1
2
3
4
5
6
7
8
Dim f As Object
Dim theFolderName As String

Set f = Application.FileDialog(3)

With f
  .InitialFileName = theFolderName
End Withs

Property.

Browse for file and get file name and path

1
2
3
4
5
6
7
8
9
10
11
12
13
'在给f路径后,用f.show来打开folder.

Dim theItem As Variant
Dim FileName, FolderPath As String

f.AllowMultiSelect = False

If f.Show Then
  For Each theItem in f.SelectedItems
    FileName = Dir(theItem)
    FolderPath = Left(theItem, Len(theItem) - Len(FileName))
  Next
End If


Import excel data

Excel 8.0: 97-2003 xls files
Excel 12.0: xlsb files
Excel 12.0 Xml: xlsx files

1
2
3
4
5
6
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase(vbNullString, False, False, "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx")

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT d.[Count] FROM [Sheet1$] AS d WHERE d.[Products] = 'Bananas';")
Debug.Print rs.Fields(0).Value

老哥的代码,其中我觉得(…)是这样的.

[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx].[Sheet1$]就可以当作一个table用了.

My version:

1
Select * INTO newTable from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Links\Products.xlsx].[Sheet1$];

是一种开盲盒的select all大法.

Import when select..<-傻子找到了但没有测.
傻子下次要import when select的时候再来补.



Object

使用With更改more than one field.
在database中的使用.

类似的在recordset中的使用.

1
2
3
4
5
6
7
8
Dim rstCategories As Recordset
Set rstCategories = CurrentDb.OpenRecordset(Name:="Categories", Type:=RecordsetTypeEnum.dbOpenDynaset)
With rstCategories
    .AddNew
    ![Category Name] = "Better software"
    !Description = "5 star rated"
    .Update
End With

在with中更改record set.



How to List Field’s Name in table in Access Using SQL

遇到了盲盒case.
要import excel data到access中但import的table会是什么样完全不知道.

于是依靠access自己去读column name,并且用resize的array来store column name.

根据两位老哥的代码改了一改.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub ListFields()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim mysteryColNames() As string '因为要resize所以先不decalre length
Dim ii as Long

Set db = CurrentDb
Set rs = db.OpenRecordSet("mysteryTable")

With rs
  ReDim Preserve mysteryColNames(.Fields.Count - 1)
  for ii = 0 to .fields.count - 1
      mysteryColNames(ii) =  .fields(ii).name
  Next
  .Close
End With

Set db = nothing
Set rs = nothing


How to check if a table exists in MS Access for vb macros

用DCount.

1
2
3
4
5
Public Function ifTableExists(tblName As String) As Boolean
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then
        ifTableExists = True
    End If
End Function