97_temp字段A,B,C,D,E,F,G,H,I,J,K,L,M,N
97cx字段A,C,D,B,H,G,E,F,I,J,K,L,M,N,NAME
97cx表示一个已经存在的表,和97_temp字段名是相同的,但是字段的顺序不同,并还多了一个字段
求详细代码解决以下问题
1、在这种情况下如何将97_temp表导进97cx表,其中NAME得值是从TEXT1.TEXT得到
2、如果在导入的时候,有重复的纪录将不会添加
我现在的代码是
Private Sub Command1_Click()
Dim db As Database
Dim sheet As String, excelpath As String, AccessPath As String, accesstable As String
AccessPath = App.Path & "\Data\97.mdb" '数据库路径
excelpath = App.Path & "\97gd\" & CommonDialog1.FileTitle 'EXCEL表格路经
accesstable = "97_temp"
sheet = Text1.Text '表格内工作表名称
Data1.DatabaseName = App.Path & "\Data\97.mdb"
If accesstable = "97_temp" Or TableDef = "97_temp" Then
Set dbs = OpenDatabase(Data1.DatabaseName)
On Error GoTo droperr
dbs.Execute "Drop table 97_temp"
droperr:
dbs.Close
End If
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Set db = OpenDatabase(excelpath, True, False, "Excel 5.0") '打开电子表格文件
SQL = ("Select * into[;database=" & AccessPath & "]." & accesstable & " FROM [" & sheet & "$]") '将电子表格导入数据库临时表97_temp
db.Execute (SQL) '将电子表格导入数据库
db.Close
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\Data\97.mdb"
exec = " insert into 97CX select * from 97_temp"
cn.Open
cn.Execute(exec) '将临时表97_temp添加进97cx表中
End Sub
97cx字段A,C,D,B,H,G,E,F,I,J,K,L,M,N,NAME
97cx表示一个已经存在的表,和97_temp字段名是相同的,但是字段的顺序不同,并还多了一个字段
求详细代码解决以下问题
1、在这种情况下如何将97_temp表导进97cx表,其中NAME得值是从TEXT1.TEXT得到
2、如果在导入的时候,有重复的纪录将不会添加
我现在的代码是
Private Sub Command1_Click()
Dim db As Database
Dim sheet As String, excelpath As String, AccessPath As String, accesstable As String
AccessPath = App.Path & "\Data\97.mdb" '数据库路径
excelpath = App.Path & "\97gd\" & CommonDialog1.FileTitle 'EXCEL表格路经
accesstable = "97_temp"
sheet = Text1.Text '表格内工作表名称
Data1.DatabaseName = App.Path & "\Data\97.mdb"
If accesstable = "97_temp" Or TableDef = "97_temp" Then
Set dbs = OpenDatabase(Data1.DatabaseName)
On Error GoTo droperr
dbs.Execute "Drop table 97_temp"
droperr:
dbs.Close
End If
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Set db = OpenDatabase(excelpath, True, False, "Excel 5.0") '打开电子表格文件
SQL = ("Select * into[;database=" & AccessPath & "]." & accesstable & " FROM [" & sheet & "$]") '将电子表格导入数据库临时表97_temp
db.Execute (SQL) '将电子表格导入数据库
db.Close
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\Data\97.mdb"
exec = " insert into 97CX select * from 97_temp"
cn.Open
cn.Execute(exec) '将临时表97_temp添加进97cx表中
End Sub
解决方案 »
- ShockwaveFlash1控件始终获得焦点
- 请问一个Form + WebBrowser的问题,急呀!有办法立刻结帖!
- 怎么让DBGrid显示的内容刷新?急!急!急!
- CSDN 真狠,把我的 Blog 帐号都搞没了!
- ^^^^^^^^^窗体问题?^^^^^^^^^
- 如何解决 Formula One 6.0 的打印预览效果比较差的问题?
- 请问DATAREPORT怎么设置横向打印啊?还有纸张的大小!横向打印预览!
- 二手VB书低价出让
- 菜鸟求教:VB中有哪些封装好的数据类型?
- 请教一个简单的问题,关于VB和 ADO
- 请问多少分升一颗五角星?
- 今天郁闷疯掉了,散300分,闲人进来说两句让我舒服一点的话吧(3)。
If gintmode = 1 Then
txtSQL = "select * from checkin where kqid='" & Trim(txtId) & "' and kqdate= '" & Format(cboYear.Text & "-" & cboMonth.Text & "-01", "yyyy-mm-dd") & "'"
Set mrc = executesql(txtSQL, MsgText)
If mrc.EOF = False Then
MsgBox "已经存在该员工在该月的考勤记录!", vbOKOnly + vbExclamation, "警告"
cboMonth.SetFocus
Exit Sub
End If
mrc.Close
End If
'先删除已有记录
txtSQL = "delete from checkin where kqid='" & Trim(txtId) & "' and kqdate='" & Format(cboYear & "-" & cboMonth & "-01", "yyyy-mm-dd") & "'"
Set mrc = executesql(txtSQL, MsgText)
'再加入新记录
txtSQL = "select * from checkin"
Set mrc = executesql(txtSQL, MsgText)
mrc.AddNew
mrc.Fields(0) = Trim(txtId)
mrc.Fields(1) = Trim(cboItem(1))
mrc.Fields(2) = Format(cboYear & "-" & cboMonth & "-01", "yyyy-mm-dd")
For intCount = 0 To 15
mrc.Fields(intCount + 3) = Trim(txtItem(intCount).Text)
Next intCount
For intCount = 16 To 17
mrc.Fields(intCount + 3) = Trim(txtItem(intCount).Text)
Next intCount
mrc.Update
If gintmode = 1 Then
MsgBox "记录添加成功!", vbOKOnly + vbExclamation, "警告"
For intCount = 0 To 17
txtItem(intCount) = ""
Next intCount
mblChange = False
frmCheck.ZOrder 0
ElseIf gintmode = 2 Then
MsgBox "记录修改成功!", vbOKOnly + vbExclamation, "警告"
Unload Me
frmCheck.ShowTitle
frmCheck.ShowData
frmCheck.ZOrder 0
End If
gintmode = 0