• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have

DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have

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




Rate DATA TRANSFER: Transfer ur data from Ms-Access, Dbase, Foxpro, Excel, CSV and Text Database. I have

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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