access进销存模板

自己建表出库单表，入库单表，格式同上EXCEL，然后创建查询设计查出对应数据即可。

这是一张进货表，采用一键导入数据库方式。

Private Sub CommandButton1_Click()

'从数据库读取供应商名称，写入名称管理器的供应商名称

'从数据库读取客户代号，写入名称管理器的客户代号

Dim rsT As New ADODB.Recordset

Dim Conn As ADODB.Connection

Dim myData As String

'Dim Arr As Object

'连接数据库

myData = ThisWorkbook.Path & "\acjxc.accdb"

Set Conn = CreateObject("ADODB.Connection") '这属于后期绑定

Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myData

Conn.ConnectionString = Conn.ConnectionString & ";Jet OLEDB:Database Password = gpflovezx@1980"

Conn.Open

'读取供应商清单

sqL = "SELECT 供应商名称 FROM GYSQD "

rsT.Open sqL, Conn, adOpenKeyset, adLockOptimistic

If rsT.RecordCount = 0 Then

MsgBox ("供应商清单为空！！！")

rsT.Close

Exit Sub

Else

Arr = rsT.GetRows

End If

rsT.Close

'输出导入结果

Range(Cells(3, 27), Cells(1000, 27)).ClearContents

For i = 0 To UBound(Arr, 2)

Cells(3 + i, 27) = Arr(0, i)

Next i

With ActiveWorkbook.Names("供应商名称")

.Name = "供应商名称"

.RefersToR1C1 = "=进!R3C27:R" & 3 + i & "C27"

.Comment = ""

End With

'读取货号

sqL = "SELECT 货号 FROM CPQD "

rsT.Open sqL, Conn, adOpenKeyset, adLockOptimistic

If rsT.RecordCount = 0 Then

MsgBox ("产品清单为空！！！")

rsT.Close

Exit Sub

Else

Arr = rsT.GetRows

End If

rsT.Close

'输出导入结果

Range(Cells(3, 28), Cells(1000, 28)).ClearContents

For i = 0 To UBound(Arr, 2)

Cells(3 + i, 28) = Arr(0, i)

Next i

With ActiveWorkbook.Names("货号")

.Name = "货号"

.RefersToR1C1 = "=进!R3C28:R" & 3 + i & "C28"

.Comment = ""

End With

'读取客户代号

sqL = "SELECT 客户代号 FROM KHQD "

rsT.Open sqL, Conn, adOpenKeyset, adLockOptimistic

If rsT.RecordCount = 0 Then

MsgBox ("客户清单为空！！！")

rsT.Close

Exit Sub

Else

Arr = rsT.GetRows

End If

rsT.Close

'输出导入结果

Range(Cells(3, 29), Cells(1000, 29)).ClearContents

For i = 0 To UBound(Arr, 2)

Cells(3 + i, 29) = Arr(0, i)

Next i

With ActiveWorkbook.Names("客户代号")

.Name = "货号"

.RefersToR1C1 = "=进!R3C29:R" & 3 + i & "C29"

.Comment = ""

End With

End Sub

这是导入数据库的代码

通过 excel+access+vba 实现小企业的进销存管理系统