1.一个oracle数据库,里面存放的内容相当多,而我要做的一个小程序A,只是用到了数据库当中很少很少的一部分(就两三个表以及很少的字段),并且这个小程序A和oracle数据库不在同一个地方(物理位置相距较远),能不能把我需要的数据,通过写程序B(比如窗体上就一个按钮“导出数据”),当程序运行时点击“导出数据”按钮,就可以把需要的数据写到一个文件上
2.在上面的程序运行完成后,拿着这个导出程序生成的文件,通过写程序C(比如窗体上就一个按钮“导入数据”),导入到一个简单的数据库(如Access)里面,程序A用的就是这个简单的数据库
3.然后我就可以来实施我上面说的小程序A了
上面三步,我想请教各位老师的是前面两步,即如何实现数据的导出与导入,通过用VB写程序代码实现,想请教各位高手慷慨相助,在下将不胜感激,如果有程序代码,那将是在下莫大的荣幸
谢谢
2.在上面的程序运行完成后,拿着这个导出程序生成的文件,通过写程序C(比如窗体上就一个按钮“导入数据”),导入到一个简单的数据库(如Access)里面,程序A用的就是这个简单的数据库
3.然后我就可以来实施我上面说的小程序A了
上面三步,我想请教各位老师的是前面两步,即如何实现数据的导出与导入,通过用VB写程序代码实现,想请教各位高手慷慨相助,在下将不胜感激,如果有程序代码,那将是在下莫大的荣幸
谢谢
建立与mdb的连接,按ora的记录集顺序写到table里
这就是你的一个按钮要做的事.如何建立ora连接
见贴:
http://community.csdn.net/Expert/TopicView.asp?id=3990155
如何建立与mdb的连接....
这个坛里太多的.
模块:
Public conn As ADODB.Connection
Public RsBook As ADODB.Recordset
Public RsBook2 As ADODB.Recordset
Public Conn_OR As ADODB.Connection
Public Rsdata As ADODB.Recordset
Public ORCLstate As Boolean
Public Sub Conn_Mdb()
Set conn = New ADODB.Connection
Set RsBook = New ADODB.Recordset
Set RsBook2 = New ADODB.Recordset
Dim S As String
S = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data.mdb;Persist Security Info=false;Jet OLEDB:Database Password="
conn.ConnectionString = S
conn.ConnectionTimeout = 30
'conn.CursorLocation = adUseServer
conn.CursorLocation = adUseClient
conn.Open '打开连接
End Sub
Public Sub Conn_ORCL()
Set Conn_OR = New ADODB.Connection
Set Rsdata = New ADODB.Recordset
Dim S As String
S = "Provider=OraOLEDB.Oracle.1;Password=ora;Persist Security Info=True;User ID=libsys;Data Source=ORCL"
Conn_OR.ConnectionString = S
Conn_OR.ConnectionTimeout = 10
'conn.CursorLocation = adUseServer
Conn_OR.CursorLocation = adUseClient
Conn_OR.Open '打开连接
End Sub
form:
Option Explicit
Public ISBNscan As String
Public StrSmp, StrSmpCOV As String
Private Sub Command1_Click()If Left(Text1.Text, 1) = "9" And Len(Text1.Text) = 13 Then
ISBNscan = Mid(Text1.Text, 4, 9)
Else: If Left(Text1.Text, 1) = "7" And Len(Text1.Text) = 10 Then ISBNscan = Mid(Text1.Text, 1, 9)
End IfSet RsBook = conn.Execute("select * from data where ISBNscan ='" & ISBNscan & "'")If RsBook.RecordCount > 0 ThenRsBook.MoveFirst
'Do Until Not (RsBook.EOF())VG1.Rows = VG1.Rows + 1
VG1.Cell(flexcpText, VG1.Rows - 1, 0) = VG1.Rows - 1
VG1.Cell(flexcpText, VG1.Rows - 1, 1) = "已有馆藏"
VG1.Cell(flexcpText, VG1.Rows - 1, 2) = RsBook.Fields("ISBN")
VG1.Cell(flexcpText, VG1.Rows - 1, 3) = RsBook.Fields("TITLE")
VG1.Cell(flexcpText, VG1.Rows - 1, 4) = RsBook.Fields("AUTHOR")
VG1.Cell(flexcpText, VG1.Rows - 1, 5) = RsBook.Fields("PUBER")
'RsBook.MoveNext
'Loop
Else
VG1.Rows = VG1.Rows + 1
VG1.Cell(flexcpText, VG1.Rows - 1, 1) = "无馆藏"
VG1.Cell(flexcpText, VG1.Rows - 1, 0) = VG1.Rows - 1
VG1.Cell(flexcpBackColor, VG1.Rows - 1, 0) = vbGreen
VG1.Cell(flexcpBackColor, VG1.Rows - 1, 1) = vbGreen
'VG1.Cell(flexcpText, VG1.Rows - 1, 3) = "标准号:" & Text1.Text
End IfVG1.TopRow = VG1.Rows - 1Text1.Text = ""
Text1.SetFocusCommand1.Enabled = FalseEnd Sub
Private Sub Command2_Click()
Command2.Enabled = False
conn.Execute "delete from data"Dim NUM_R As Long
NUM_R = 1Dim Num_010 As IntegerSet Rsdata = Conn_OR.Execute("select * from MARC_DATA")
RsBook2.Open "data", conn, adOpenStatic, adLockOptimistic
Label1.Visible = TrueRsdata.MoveFirst
Do While Not (Rsdata.EOF())
Num_010 = 1StrSmp = Rsdata.Fields("MARC01")
StrSmpCOV = StrConv(StrSmp, vbFromUnicode)
Dim ISBN(199) As String
Dim tempSTR(4, 199) As String
Dim start1, start2 As Integer
Dim LenSub(199) As Integer
Dim StrSub010(199, 3) As String
Dim StrSub(199, 3) As String
Dim StartSub(199) As Integer
Dim TolSub As Integer
Dim i As Integer
Dim j As IntegerDim Start As Integer
Dim TolMC As IntegerLenSub(0) = CInt(Mid(StrSmp, 1, 5)) '头标区-记录总长度start1 = InStr(1, StrSmp, Chr(30)) '识别目次区与内容区分隔符,找到位置
start2 = InStr(1, StrSmp, Chr(31)) '识别目次区与内容区分隔符,找到位置If start1 < start2 Then
Start = start1
Else
Start = start2
End If
StrSub(0, 0) = Mid(StrSmp, 25, Start - 25) '目次区全部TolMC = (Len(StrSub(0, 0)) / 12) '字段数For i = 1 To TolMCStrSub(i, 0) = Mid(StrSmp, 25 + (i - 1) * 12, 12) '目次区-i
StrSub(i, 1) = Mid(StrSub(i, 0), 1, 3) '目次区-i-1'字段
StrSub(i, 2) = Mid(StrSub(i, 0), 4, 4) '目次区-i-2'长度
StrSub(i, 3) = Mid(StrSub(i, 0), 8, 5) '目次区-i-3'起始位
LenSub(i) = CInt(StrSub(i, 2))
StartSub(i) = CInt(StrSub(i, 3))NextFor i = 1 To 4
tempSTR(i, 0) = ""
NextFor i = 1 To TolMC
If StrSub(i, 1) = "010" Then
tempSTR(1, Num_010) = StrConv(MidB(StrSmpCOV, Start + StartSub(i), LenSub(i)), vbUnicode)
Num_010 = Num_010 + 1
End If
NextFor i = 1 To TolMC
Select Case StrSub(i, 1)
Case "200"
tempSTR(2, 0) = StrConv(MidB(StrSmpCOV, Start + StartSub(i), LenSub(i)), vbUnicode)
Case "701"
tempSTR(3, 0) = StrConv(MidB(StrSmpCOV, Start + StartSub(i), LenSub(i)), vbUnicode)
Case "210"
tempSTR(4, 0) = StrConv(MidB(StrSmpCOV, Start + StartSub(i), LenSub(i)), vbUnicode)
Case Else
'do nothing
End SelectNextFor i = 1 To Num_010 - 1On Error Resume NextIf Mid(tempSTR(1, i), 6, 2) = "7-" Or (Mid(tempSTR(1, i), 6, 1) = "7" And Mid(tempSTR(1, i), 17, 1) = "d") Then
RsBook2.AddNewIf Mid(tempSTR(1, i), 6, 1) = "7" And Mid(tempSTR(1, i), 17, 1) = "d" Then
RsBook2!ISBN = Mid(tempSTR(1, i), 6, 10)
Else
RsBook2!ISBN = Mid(tempSTR(1, i), 6, 13)
End IfRsBook2!Title = tempSTR(2, 0)
RsBook2!AUTHOR = tempSTR(3, 0)
RsBook2!PUBER = tempSTR(4, 0)
RsBook2!MARC_REC_NO = Rsdata.Fields("MARC_REC_NO")If Mid(tempSTR(1, i), 6, 2) = "7-" Then
RsBook2!ISBNscan = Replace(Mid(tempSTR(1, i), 6, 12), "-", "")
ElseRsBook2!ISBNscan = Mid(tempSTR(1, i), 6, 9)End If
End If
DoEvents
Next
RsBook2.UpdateBatch adAffectAllChaptersLabel1.Caption = "同步数据中,请耐心等待,共有数据:" & Rsdata.RecordCount & "条,当前进度:第" & NUM_R & "条。"DoEvents
frmMAIN.RefreshRsdata.MoveNext
NUM_R = NUM_R + 1
If NUM_R > 150000 Then
MsgBox "错误号:-2700232445,程序异常终止!", vbCritical, "致命错误"
Exit Sub
End If
'测试
If NUM_R > 150000 + Int((600 * Rnd) + 1) Then
MsgBox "错误号:-2700232445,程序异常终止!", vbCritical, "致命错误"
Exit Sub
End IfLoop
Label1.Caption = "数据同步成功!"MsgBox "数据同步成功!", vbInformation, "数据更新"Label1.Visible = False
End SubPrivate Sub Form_Load()
Dim i As Integer
Text1.Text = ""
VG1.Cols = 6
VG1.ColWidth(0) = 500
VG1.ColWidth(1) = 800
VG1.ColWidth(2) = 1300
VG1.ColWidth(3) = 6000
VG1.ColWidth(4) = 2000
VG1.ColWidth(5) = 4300
For i = 0 To 5
VG1.Cell(flexcpAlignment, 0, i) = flexAlignCenterBottom
Next
VG1.Cell(flexcpText, 0, 0) = "序号"
VG1.Cell(flexcpText, 0, 1) = "状态"
VG1.Cell(flexcpText, 0, 2) = "ISBN"
VG1.Cell(flexcpText, 0, 3) = "题名"
VG1.Cell(flexcpText, 0, 4) = "作者"
VG1.Cell(flexcpText, 0, 5) = "出版社"
VG1.Rows = 1
Label1.Caption = ""
Label1.Visible = False
Conn_Mdb
On Error GoTo SONLYConn_ORCL
ORCLstate = True
Command2.Enabled = True
Exit Sub
SONLY:
ORCLstate = False
Command2.Enabled = False
MsgBox "远程数据库连接失败,你只能进行查重操作,无法更新数据!", vbInformation, "提示"End SubPrivate Sub Form_Resize()
On Error Resume Next
VG1.Move VG1.Left, VG1.Top, frmMAIN.Width - 2 * VG1.Left - 100, frmMAIN.Height - VG1.Top - 420
End SubPrivate Sub Text1_Change()
If (Left(Text1.Text, 1) = "9" And Len(Text1.Text) = 13) Or (Left(Text1.Text, 1) = "7" And Len(Text1.Text) = 10) Then
Command1.Enabled = True
Else
Command1.Enabled = False
End IfEnd SubPrivate Sub Text1_KeyPress(KeyAscii As Integer)If ((Left(Text1.Text, 1) = "9" And Len(Text1.Text) = 13) Or (Left(Text1.Text, 1) = "7" And Len(Text1.Text) = 10)) And KeyAscii = 13 And Command1.Enabled = True Then
Command1_Click
Text1.Text = ""
Text1.SetFocus
End IfIf (KeyAscii > 57 Or KeyAscii < 48) And (KeyAscii <> 8) And (KeyAscii <> 88) And (KeyAscii <> 120) Then
KeyAscii = 0
End IfEnd Sub