by Chaudhary Pradeep Kumar roy (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Mon 12th May 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have used this code for reading data from all
API Declarations
'\ 4 Labels with caption "data Source","File Name","UID","PWD"
' \one combo box named "cmbDataSource"
'\ 3 Text Box named "txtFileName", "txtUID with text Admin","txtPWD"
'\ One common dialog control named "cdlgbrow"
'\ 3 Command Buttons named "cmdbrow","cmdConnect","cmdClose"
'\ Jusr run the code and select data source, Select fiel name, click on connect Button
'\ I am also developign a data transfering utility using that u can transfer ur data from any source data base to any target database .. I am trying to include XML as well. Happy coding enjoy..
'\\\\\\\\\\\\\\ CODE FOLLOWS FROM HERE \\\\\\\\\\\\\\\\\\\\'\ Declearing constants for Data sources
'\ Put a common Dialog Box and set reference to ADO
Private Const sDB_ACCESS97 = "Microsoft Access Database 97"
Private Const sDB_ACCESS2K = "Microsoft Access Database 2000"
Private Const sDB_DBASE = "Dbase Database"
Private Const sDB_FOXPRO = "FoxPro Database"
Private Const sDB_EXCEL = "Excel File"
Private Const sDB_CSV = "Comma Separated Values"
Private Const sDB_TXT = "Text Database"
Private gSConn As New ADODB.Connection '\ Source Connection
Private gTConn As New ADODB.Connection '\ Target Connection
Private sDBName As String, sUID As String, sPWD As String
VBcoders
If cmbDataSource.Text = sDB_ACCESS2K Or cmbDataSource.Text = sDB_ACCESS97 Then
txtUID.Locked = True
txtPWD.Locked = True
Else
txtUID.Locked = False
txtPWD.Locked = False
End If
End Sub
Private Sub cmdbrowse_Click()
cdlgbrow.InitDir = App.Path
'\ Just put the filter in Common dialog control as per the selected data source
cdlgbrow.ShowOpen
If cdlgbrow.FileName <> "" Then
txtFileName = Trim(cdlgbrow.FileName)
End If
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdConnect_Click()
'\ Valida the data here before trying to connecting like.. File name and UID PWD where applicable can not be blank
sDBName = Trim(txtFileName)
sUID = Trim(txtUID)
sPWD = Trim(txtPWD)
'\\ Connecting to Source
If bConnectToSource Then
dPortMain.Caption = "#dPort - [Connected to " & sDBName & "]"
Unload Me
Else
dPortMain.Caption = "#dPort - [Not connected to datasource]"
End If
End Sub
Private Sub Form_Load()
Call BpopulateDataSource '\ Populate Data Source
End Sub
Private Function BpopulateDataSource()
'\ This fun will populate the datasource
cmbDataSource.Clear
cmbDataSource.AddItem sDB_ACCESS97
cmbDataSource.AddItem sDB_ACCESS2K
cmbDataSource.AddItem sDB_DBASE
cmbDataSource.AddItem sDB_FOXPRO
cmbDataSource.AddItem sDB_EXCEL
cmbDataSource.AddItem sDB_CSV
cmbDataSource.AddItem sDB_TXT
End Function
Private Function bConnectToSource() As Boolean
On Error GoTo ErrHand
'\ Function To estrablish the connection and to open the recordset
bConnectToSource = False
Dim sConnStr As String
Dim rs As New ADODB.Recordset
If cmbDataSource = sDB_ORACLE Then
sConnStr = "Driver={Microsoft ODBC Driver for Oracle};" & _
"Server=dbserver.nhpc;" & _
"Uid=tcstest;" & _
"Pwd=test"
gSConn.Open sConnStr
ElseIf cmbDataSource = sDB_ACCESS97 Then
sConnStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & sDBName & ";Uid=" & sUID & "; Pwd=" & sPWD
gSConn.Open sConnStr
ElseIf cmbDataSource = sDB_ACCESS2K Then
sConnStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & sDBName & ";Uid=" & sUID & "; Pwd=" & sPWD
gSConn.Open sConnStr
gSConn.OpenSchema adSchemaTables
ElseIf cmbDataSource = sDB_DBASE Then
sConnStr = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & sGetFilePath(sDBName)
gSConn.Open sConnStr
rs.Open "Select * From DbaseDB.dbf", gSConn, , , adCmdText
ElseIf cmbDataSource = sDB_FOXPRO Then
sConnStr = "Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=" & sGetFilePath(sDBName) & "; Exclusive=No;"
gSConn.Open sConnStr
ElseIf cmbDataSource = sDB_EXCEL Then
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim(sDBName) & ";Extended Properties=Excel 8.0"
gSConn.Open sConnStr
ElseIf cmbDataSource = sDB_CSV Then
sConnStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sGetFilePath(sDBName) & ";Extensions=asc,csv,tab,txt"
gSConn.Open sConnStr
ElseIf cmbDataSource = sDB_TXT Then
sConnStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sGetFilePath(sDBName) & ";Extensions=asc,csv,tab,txt"
gSConn.Open sConnStr
End If
bConnectToSource = True
Exit Function
ErrHand:
MsgBox Err.Description, , "Error Number: " & Err.Number
bConnectToSource = False
End Function
Function sGetFilePath(sPath As String) As String
'\this function strips the file name from a path\file string
On Error Resume Next
Dim i As Integer
For i = Len(sPath) To 1 Step -1
If Mid(sPath, i, 1) = "\" Then
Exit For
End If
Next
sGetFilePath = Mid(sPath, 1, i - 1)
End Function
No comments have been posted about DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have. Why not be the first to post a comment about DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have.
