推薦：揭秘十萬(wàn)條Access數(shù)據(jù)表分頁(yè)的解決方法
后臺(tái)數(shù)據(jù)庫(kù)用是Access,客戶用了一年后說(shuō)打開(kāi)界面非常慢,查看了數(shù)據(jù)庫(kù)后發(fā)現(xiàn)數(shù)據(jù)表中的記錄已有五萬(wàn)多條,自己試過(guò)將記錄復(fù)制到10 萬(wàn)條,打開(kāi)界面非常慢,翻頁(yè)也是同樣的問(wèn)題,我采用的方

本文向你介紹如何不用借助Access，直接在程序中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，然后從標(biāo)準(zhǔn)的ASCII文本文件中讀取數(shù)據(jù)到數(shù)據(jù)庫(kù)中。原文是微軟知識(shí)庫(kù)中的一篇文章，但當(dāng)時(shí)是針對(duì)VB3寫(xiě)的，所以其中的代碼有點(diǎn)過(guò)時(shí)。例如現(xiàn)在DAO中已沒(méi)有Table對(duì)象，代之以Recordset對(duì)象。下面是修改后的代碼，在VB6中調(diào)試通過(guò)。

首先在工程中添加對(duì)Microsoft DAO 3.51 Library引用。

在窗體中添加三個(gè)命令按鈕和兩個(gè)MSFlexGrid.

按照下表設(shè)置和控件的屬性：

控件 屬性 值

--------------------------------------------------------------------

Command1 Caption "建立文本文件并顯示在網(wǎng)格中"

Command2 Caption "傳輸入數(shù)據(jù)并新建一個(gè)數(shù)據(jù)庫(kù)"

Command3 Caption "顯示新數(shù)據(jù)庫(kù)中的數(shù)據(jù)"

Grid1 Cols 5

Grid1 Rows 35

Grid2 Cols 5

Grid2 Rows 35

將下面的代碼添加到窗體的聲明部分

Dim nums(30) As Long

Dim names(30) As String * 20

Dim addresses(30) As String * 25

Dim ss_nums(30) As String * 12

Const DB_LONG = 4

Const DB_TEXT = 10

Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"

將下面的代碼添加到窗體的Load事件中

Sub Form_Load ()

Show

grid1.ColWidth(1) = 1000 'For Emp ID

grid1.ColWidth(2) = 2000 'For Emp Name

grid1.ColWidth(3) = 3000 'For Emp Addr

grid1.ColWidth(4) = 2000 'For Emp SSN

grid1.Col = 1

grid1.Row = 0

grid1.Text = "Emp ID" 'Header for Emp ID from text file

grid1.Col = 2

grid1.Row = 0

grid1.Text = "Emp Name" 'Header for Emp Name from text file

grid1.Col = 3

grid1.Row = 0

grid1.Text = "Emp Addr" 'Header for Emp Addr from text file

grid1.Col = 4

grid1.Row = 0

grid1.Text = "Emp SSN" 'Header for Emp SSN from text file

grid2.ColWidth(1) = 1000 'For Emp ID

grid2.ColWidth(2) = 2000 'For Emp Name

grid2.ColWidth(3) = 3000 'For Emp Addr

grid2.ColWidth(4) = 2000 'For Emp SSN

grid2.Col = 1

grid2.Row = 0

grid2.Text = "Employee ID" 'Header for Emp ID from DB

grid2.Col = 2

grid2.Row = 0

grid2.Text = "Employee Name" 'Header for Emp Name from DB

grid2.Col = 3

grid2.Row = 0

grid2.Text = "Employee Addr" 'Header for Emp ID from DB

grid2.Col = 4

grid2.Row = 0

grid2.Text = "Employee SSN" 'Header for Emp Name from DB

End Sub

在Command1_Click事件中加入下面的代碼

Sub Command1_Click ()

For i% = 1 To 30

nums(i%) = i%

names(i%) = "John Doe # " Str$(i%)

addresses(i%) = Str$(i%) " Mocking Bird Lane"

If i% < 9 Then

'* Enter the following four lines as one, single line:

ss_nums(i%) = Trim$(Str$(i%) Trim$(Str$(i%))

Trim$(Str$(i%)) "-" Trim$(Str$(i% 1))

Trim$(Str$(i% 1)) "-" Trim$(Str$(i%))

Trim$(Str$(i%)) Trim$(Str$(i%)) Trim$(Str$(i%)))

Else

'* Enter the following two lines as one, single line:

ss_nums(i%) = Trim$(Trim$(Str$(999)) "-" Trim$(Str$(88))

"-" Trim$(Str$(7777)))

End If

Next i%

Open "Testdata.DAT" For Output As #1

For j% = 1 To 30

Print #1, nums(j%)

Print #1, names(j%)

Print #1, addresses(j%)

Print #1, ss_nums(j%)

Next j%

Close #1

For i% = 1 To 30 'Display results from text file

grid1.Col = 1

grid1.Row = i%

grid1.Text = nums(i%) 'Load Emp IDs

grid1.Col = 2

grid1.Row = i%

grid1.Text = names(i%) 'Load Emp Names

grid1.Col = 3

grid1.Row = i%

grid1.Text = addresses(i%) 'Load Emp Addrs

grid1.Col = 4

grid1.Row = i%

grid1.Text = ss_nums(i%) 'Load Emp SSNs

Next i%

End Sub

在Command2_Click事件中加入下面的代碼

Sub Command2_Click ()

Dim newdb As Database

Dim newtb As Table

Dim newtd As New tabledef

Dim newidx As New Index

Dim field1 As New field 'For Emp nums

 

Dim field2 As New field 'For Emp names

Dim field3 As New field 'For Emp addresses

Dim field4 As New field 'For Emp ss_nums

screen.MousePointer = 11 'Display the time to build

Set newdb = CreateDatabase("NEWDB.MDB", DB_LANG_GENERAL)

newtd.Name = "Emp_Table" '* New table name

field1.Name = "Emp_ID" '* Holds Employee ID nums()

field1.Type = DB_LONG

newtd.Fields.Append field1

field2.Name = "Emp_Name" '* Holds Emp names()

field2.Type = DB_TEXT

field2.Size = 20

newtd.Fields.Append field2

field3.Name = "Emp_Addr" '* Holds Employee addr()

field3.Type = DB_TEXT

field3.Size = 25

newtd.Fields.Append field3

field4.Name = "Emp_SSN" '* Holds emp ss_nums()

field4.Type = DB_TEXT

field4.Size = 12

newtd.Fields.Append field4

newidx.Name = "Emp_ID_IDX" '* You have to have an index

newidx.Fields = "Emp_ID"

newidx.Primary = True

newtd.Indexes.Append newidx

newdb.TableDefs.Append newtd

Set newtb = newdb.OpenTable("Emp_Table")

Open "Testdata.dat" For Input As #1

BeginTrans

Do While Not (EOF(1))

newtb.AddNew

Line Input #1, tmp1$ 'Retrieve empl_id

Line Input #1, tmp2$ 'Retrieve empl_name

Line Input #1, tmp3$ 'Re

分享：解析在Access中模擬sql server存儲(chǔ)過(guò)程翻頁(yè)
sql server中翻頁(yè)存儲(chǔ)過(guò)程: Create PROC blog_GetPagedPosts ( @PageIndex int, @PageSize int, @BlogID int=0, @PostType int=-1, @CategoryID int=-1, @Hiding