by Chaudhary Pradeep Kr Roy (6 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 12th April 2003
Date Added: Mon 8th February 2021
Rating: (1 Votes)
Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have used this code for reading data from all mentioned data
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
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 Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have used this code. Why not be the first to post a comment about Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have used this code.