Copy Table from One computer to another in network using OleDB.

R

remya1000

Well-known member
Joined
Mar 29, 2007
Messages
122
Programming Experience
Beginner
i'm using VB.NET

i have 4 computers in network.
when i press a button i need to delete a table from computer 2,3,4 and i need to copy that table from computer 1 to 2,3,4.
and this file is a table inside a .mdb file. so i can't use "FILECOPY(sOURCE,DESTINATION)".

Eg: if the path is "c:\Programs\data.mdb" ,then Emp is one of the table inside that data.mdb. and i don't need to delete entire data.mdb, i need to delete only that "Emp" table and i don't need to copy entire data.mdb, i need to copy only "Emp" table and save it in computers 2,3,4.

if anyone have anyidea how to delete and copy this table inside .mdb file, to other computers in network.

if you have any idea please let me know. and if you can provide eg: that will be great help for me.

thanks in advance.
 
Sort by date Sort by votes
i tried this code....

VB.NET: 
 Dim nPath As String = "\\t1\root\Programs"
        Dim strConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & nPath & "\DataTables.mdb"


        Dim strSQL1 As String = "Select * from Employees"
        Dim myConnection1 As New OleDbConnection(strConn1)
        myConnection1.Open()
        Dim myCommand1 As New OleDbCommand(strSQL1, myConnection1)
        Dim myReader1 As OleDbDataReader = myCommand1.ExecuteReader

        While myReader1.Read
            Dim nPath2 As String = "\\t2\root\Programs"
            Dim strConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                           "Data Source=" & nPath2 & "\Terminal.mdb"

           Dim strSQL2 As String = "insert into Employees (EmpID, FirstName, LastName, AccessN0,CardNo,InsuranceNo) values (" & Trim(myReader1(0)) & ", '" & Trim(myReader1(1)) & "', '" & Trim(myReader1(2)) & "', " & Trim(myReader1(3)) & ", " & Trim(myReader1(4)) & ", " & Trim(myReader1(5)) & ", " & Trim(myReader1(6)) & ") "
            Dim myConnection2 As New OleDbConnection(strConn2)
            myConnection2.Open()
            Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
            myCommand2.ExecuteNonQuery()
        End While

       myReader1.Close()
        myConnection1.Close()
Sometimes some of the fields will be blank. and while trying to insert the values to computer 2 from computer 1 error occurs as

Error:
------
"An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll. Additional information: Cast from type 'DBNull' to type 'String' is not valid."

if anyone have anyidea what's wrong in my codes please help me.

thanks in advance.
 
Upvote 0 Downvote
i tryied this code too....

VB.NET: 
        Dim nPath As String = "\\t1\root\Programs"
        Dim strConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & nPath & "\DataTables.mdb"

        Dim strSQL1 As String = "Select * from Emp"
        Dim myConnection1 As New OleDbConnection(strConn1)
        myConnection1.Open()
        Dim myCommand1 As New OleDbCommand(strSQL1, myConnection1)
        Dim myReader1 As OleDbDataReader = myCommand1.ExecuteReader

While myReader1.Read
            Dim nPath2 As String = "t2\\root\Programs"
            Dim strConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                           "Data Source=" & nPath2 & "\DataTable.mdb"

           Dim strSQL2 As String = "insert into Emp (EmployeeID, EmployeePrivateAccessNumber,SwipeCardNumber,SocialInsuranceNumber, FirstName, LastName, Title, Address, City, State, PostalCode, HomePhone, EmrgcyContactName, EmrgcyContactPhone, IsThisPersonAManager, OpenWhichCashDrawer, AccessToManagerScreen, AllowedToGiveDiscounts, AllowedToDeleteSentItems, AllowedToCompItems, AllowedToCashOut, AllowedToAccessOtherOrders, AllowedToChangeItems, AllowedToRunReports, AllowedToQuickCashOut, WhichMenuLevel, index, StartOnQuickScreen, HourlyWage, AllowedToTransferOrders, Dancer, StageName, Age, DepartmentID, ListStyle, AccessToServerSide, Driver, AccessToPrintSpooler, BusBoy, WhichRoom, AccessToHouseAccounts, ShareTips, JobCode1, JobCode2, JobCode3, JobCode4, JobCode5, JobCode6, JobCode7, JobCode8, JobCode9, JobCode10, JobCode1HourlyWage, JobCode2HourlyWage, JobCode3HourlyWage, JobCode4HourlyWage, JobCode5HourlyWage, JobCode6HourlyWage, JobCode7HourlyWage, JobCode8HourlyWage, JobCode9HourlyWage, JobCode10HourlyWage, Message, Cashier, TipOutToBarStaffOnLogOut, TipOutToKitchenStaffOnLogOut, AllowedToEmail, MultiSelectOnMainMenuList, RecordLogOutInfo, TrainingMode, InActive, Salaried, DeliveryDispatcher, ForceDrawer1_2, JobCodeProfile, CompValue, CashLimit, ServerKey, ClearviewKey, LanguagePreference, AccessLevel) " & _
                                    "values ('" & CheckNull(Trim(myReader1(0))) & "', '" & CheckNull(Trim(myReader1(1))) & "', '" & Trim(myReader1(2)) & "', '" & CheckNull(Trim(myReader1(3))) & "', '" & CheckNull(Trim(myReader1(4))) & "', '" & CheckNull(Trim(myReader1(5))) & "', '" & CheckNull(Trim(myReader1(6))) & "', '" & CheckNull(Trim(myReader1(7))) & "', '" & CheckNull(Trim(myReader1(8))) & "', '" & CheckNull(Trim(myReader1(9))) & "', '" & CheckNull(Trim(myReader1(10))) & "', '" & CheckNull(Trim(myReader1(11))) & "', '" & CheckNull(Trim(myReader1(12))) & "', '" & CheckNull(Trim(myReader1(13))) & "', '" & CheckNull(Trim(myReader1(14))) & "', '" & CheckNull(Trim(myReader1(15))) & "', '" & CheckNull(Trim(myReader1(16))) & "', '" & CheckNull(Trim(myReader1(17))) & "', '" & CheckNull(Trim(myReader1(18))) & "', '" & CheckNull(Trim(myReader1(19))) & "', '" & CheckNull(Trim(myReader1(20))) & "', '" & CheckNull(Trim(myReader1(21))) & "', '" & CheckNull(Trim(myReader1(22))) & "', '" & CheckNull(Trim(myReader1(23))) & "', '" & CheckNull(Trim(myReader1(24))) & "', '" & CheckNull(Trim(myReader1(25))) & "', '" & CheckNull(Trim(myReader1(26))) & "', '" & CheckNull(Trim(myReader1(27))) & "', '" & CheckNull(Trim(myReader1(28))) & "', '" & CheckNull(Trim(myReader1(29))) & "', '" & CheckNull(Trim(myReader1(30))) & "', '" & CheckNull(Trim(myReader1(31))) & "', '" & CheckNull(Trim(myReader1(32))) & "', '" & CheckNull(Trim(myReader1(33))) & "', '" & CheckNull(Trim(myReader1(34))) & "', '" & CheckNull(Trim(myReader1(35))) & "', '" & CheckNull(Trim(myReader1(36))) & "', '" & CheckNull(Trim(myReader1(37))) & "', '" & CheckNull(Trim(myReader1(38))) & "', '" & CheckNull(Trim(myReader1(39))) & "', '" & CheckNull(Trim(myReader1(40))) & "', '" & CheckNull(Trim(myReader1(41))) & "', '" & CheckNull(Trim(myReader1(42))) & "', '" & CheckNull(Trim(myReader1(44))) & "', '" & CheckNull(Trim(myReader1(45))) & "', '" & CheckNull(Trim(myReader1(46))) & "', '" & CheckNull(Trim(myReader1(47))) & "', '" & CheckNull(Trim(myReader1(48))) & "', '" & CheckNull(Trim(myReader1(49))) & "', '" & CheckNull(Trim(myReader1(51))) & "', '" & CheckNull(Trim(myReader1(52))) & "', '" & CheckNull(Trim(myReader1(53))) & "', '" & CheckNull(Trim(myReader1(54))) & "', '" & CheckNull(Trim(myReader1(55))) & "', '" & CheckNull(Trim(myReader1(56))) & "', '" & CheckNull(Trim(myReader1(57))) & "', '" & CheckNull(Trim(myReader1(58))) & "', '" & CheckNull(Trim(myReader1(59))) & "', '" & CheckNull(Trim(myReader1(60))) & "', '" & CheckNull(Trim(myReader1(61))) & "', '" & CheckNull(Trim(myReader1(62))) & "', '" & CheckNull(Trim(myReader1(62))) & "', '" & CheckNull(Trim(myReader1(63))) & "', '" & CheckNull(Trim(myReader1(64))) & "', '" & CheckNull(Trim(myReader1(65))) & "', '" & CheckNull(Trim(myReader1(66))) & "', '" & CheckNull(Trim(myReader1(67))) & "', '" & CheckNull(Trim(myReader1(68))) & "', '" & CheckNull(Trim(myReader1(69))) & "', '" & CheckNull(Trim(myReader1(70))) & "', '" & CheckNull(Trim(myReader1(71))) & "', '" & CheckNull(Trim(myReader1(72))) & "', '" & CheckNull(Trim(myReader1(73))) & "', '" & CheckNull(Trim(myReader1(74))) & "', '" & CheckNull(Trim(myReader1(75))) & "', '" & CheckNull(Trim(myReader1(76))) & "', '" & CheckNull(Trim(myReader1(77))) & "', '" & CheckNull(Trim(myReader1(78))) & "', '" & CheckNull(Trim(myReader1(79))) & "', '" & CheckNull(Trim(myReader1(80))) & "') "
           
            Dim myConnection2 As New OleDbConnection(strConn2)
            myConnection2.Open()
            Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
            myCommand2.ExecuteNonQuery()
        End While

        myReader1.Close()
        myConnection1.Close()
        myCommand1.Dispose()


 Public Function CheckNull(ByVal Value As String) As String
        If IsDBNull(Value) Then
            Return ("")
        ElseIf Value = "." Then
            Return ("")
        ElseIf Value = "" Then
            Return ("")
        Else
            Return CStr(Value)
        End If
    End Function

and while running its passing through SQL. and that its taking vlues too. and i'm pasting that SQL taking values during run time here.

"insert into Emp (EmployeeID, EAccessNumber, ........etc) values (4192, 9, 112, ., 'Manager', 'Manager', '.', '.', '.', '.', '.', '.', '.', '.', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 78, 0, 0, 1, '0', ., 0, 1, 0, 0, 0, 0, 0, 302, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ., '.', 0, 0, 0, 1, 1, 2, 0, 0, 0, 0, 1, 0, 0, 0, 4, 300101, 0, 0) "

and when it reach this myCommand2.ExecuteNonQuery() line error occurs as below.
Error
-----
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

if anyone have anyidea what's the mistake i did in my codes, please let me know. please help me...

thanks in advance...
 
Upvote 0 Downvote
and i insert the TRy Catch in my codes.
VB.NET: 
Dim nPath As String = "\\t1\root\Programs"
        Dim strConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & nPath & "\DataTables.mdb"

        Dim strSQL1 As String = "Select * from Emp"
        Dim myConnection1 As New OleDbConnection(strConn1)
        myConnection1.Open()
        Dim myCommand1 As New OleDbCommand(strSQL1, myConnection1)
        Dim myReader1 As OleDbDataReader = myCommand1.ExecuteReader

While myReader1.Read
            Dim nPath2 As String = "t2\\root\Programs"
            Dim strConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                           "Data Source=" & nPath2 & "\DataTable.mdb"

  Try
                Dim strSQL2 As String = "insert into Emp (EmployeeID, EmployeePrivateAccessNumber,SwipeCardNumber,SocialInsuranceNumber, FirstName, LastName, Title, Address, City, State, PostalCode, HomePhone, EmrgcyContactName, EmrgcyContactPhone, IsThisPersonAManager,OpenWhichCashDrawer, AccessToManagerScreen, AllowedToGiveDiscounts, AllowedToDeleteSentItems, AllowedToCompItems, AllowedToCashOut, AllowedToAccessOtherOrders, AllowedToChangeItems, AllowedToRunReports, AllowedToQuickCashOut, WhichMenuLevel, StartOnQuickScreen, HourlyWage, AllowedToTransferOrders, Dancer, StageName, Age, DepartmentID, ListStyle, AccessToServerSide, Driver, AccessToPrintSpooler, BusBoy, WhichRoom, AccessToHouseAccounts, ShareTips, JobCode1, JobCode2, JobCode3, JobCode4, JobCode5, JobCode6, JobCode7, JobCode8, JobCode9, JobCode10, JobCode1HourlyWage, JobCode2HourlyWage, JobCode3HourlyWage, JobCode4HourlyWage, JobCode5HourlyWage, JobCode6HourlyWage, JobCode7HourlyWage, JobCode8HourlyWage, JobCode9HourlyWage, JobCode10HourlyWage, Message, Cashier, TipOutToBarStaffOnLogOut, TipOutToKitchenStaffOnLogOut, AllowedToEmail, MultiSelectOnMainMenuList, RecordLogOutInfo, TrainingMode, InActive, Salaried, DeliveryDispatcher, ForceDrawer1_2, JobCodeProfile, CompValue, CashLimit, ServerKey, ClearviewKey, LanguagePreference, AccessLevel) " & _
                                  "values (" & EmpId & ", " & AccessNo & ", " & Cardno & ", '" & SINno & "', '" & FName & "', '" & LName & "', '" & eTitle & "','" & eAddress & "', '" & eCity & "', '" & eState & "', '" & ePin & "', '" & eHomePhone & "', '" & eEmyContactName & "', '" & eEmyContactPhone & "', " & isManager & ", " & eOpenCashDraw & ", " & ManagerScreen & ", " & GiveDiscount & ", " & delsend & ", " & eCompItem & ", " & eCashOut & ", " & eAccessOrder & ", " & eChangeitems & ", " & eRunReport & ", " & eQuickcashOut & ", " & eMenuLevel & ", " & eQuickscreen & ", " & eHourWage & ", " & eTransferOrder & ", " & eDance & ", '" & eStageName & "', " & eAge & ", " & eDepId & ", " & eListStyle & ", " & eServerSide & ", " & eDriver & ", " & ePrintSpooler & ", " & eBusBoy & ", " & eWhichRoom & ", " & eHouseAccount & ", " & Trim(myReader1(41)) & ", " & Trim(myReader1(42)) & ", " & Trim(myReader1(43)) & ", " & Trim(myReader1(44)) & ", " & Trim(myReader1(45)) & ", " & Trim(myReader1(46)) & ", " & Trim(myReader1(47)) & ", " & Trim(myReader1(48)) & ", " & Trim(myReader1(49)) & ", " & Trim(myReader1(50)) & ", " & Trim(myReader1(51)) & ", " & Trim(myReader1(52)) & ", " & Trim(myReader1(53)) & ", " & Trim(myReader1(54)) & ", " & Trim(myReader1(55)) & ", " & Trim(myReader1(56)) & ", " & Trim(myReader1(57)) & ", " & Trim(myReader1(58)) & ", " & Trim(myReader1(59)) & ", " & Trim(myReader1(60)) & ", " & Trim(myReader1(61)) & ", '" & emessage & "', " & Trim(myReader1(63)) & ", " & Trim(myReader1(64)) & ", " & Trim(myReader1(65)) & ", " & Trim(myReader1(66)) & ", " & Trim(myReader1(67)) & ", " & Trim(myReader1(68)) & ", " & Trim(myReader1(69)) & ", " & Trim(myReader1(70)) & ", " & Trim(myReader1(71)) & ", " & Trim(myReader1(72)) & ", " & Trim(myReader1(73)) & ", " & Trim(myReader1(74)) & ", " & Trim(myReader1(75)) & ", " & Trim(myReader1(76)) & ", " & Trim(myReader1(77)) & ", " & Trim(myReader1(78)) & ", " & Trim(myReader1(79)) & ", " & Trim(myReader1(80)) & ") "
                Dim myConnection2 As New OleDbConnection(strConn2)
                myConnection2.Open()
                Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
                myCommand2.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(Err.Number & " - " & ex.Message)
            End Try
           
            Dim myConnection2 As New OleDbConnection(strConn2)
            myConnection2.Open()
            Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
            myCommand2.ExecuteNonQuery()
        End While

        myReader1.Close()
        myConnection1.Close()
        myCommand1.Dispose()

sometimes it take values from T1 and writes to T2. sometimes i get this error
"5 - Could not update; currently locked by user 'Admin' on machine 'T2'."
Click to expand...

and before showing this error some of the records got saved in mdb. and ecah time the number of records enter in mdb before showing this error is random. sometime all the recorsd will be saved and it won't show this error too.

and database is not open while running the code. and no other calls are made to the database at that time too.

if you have any idea what's wrong in my codes please help me. and please let me know. please help me.

thanks in advance.
 
Upvote 0 Downvote
heah its working now......
that error occurs because myConnection2 is opening each time while its enter while loop but its not closing.

Thanks a lot for your help..... thanks a lot.....

VB.NET: 
Dim nPath As String = "\\t1\root\Programs"
        Dim strConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & nPath & "\DataTables.mdb"

        Dim strSQL1 As String = "Select * from Emp"
        Dim myConnection1 As New OleDbConnection(strConn1)
        myConnection1.Open()
        Dim myCommand1 As New OleDbCommand(strSQL1, myConnection1)
        Dim myReader1 As OleDbDataReader = myCommand1.ExecuteReader

While myReader1.Read
            Dim nPath2 As String = "t2\\root\Programs"
            Dim strConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                           "Data Source=" & nPath2 & "\DataTable.mdb"

  Try
                Dim strSQL2 As String = "insert into Emp (EmployeeID, ...... ) " & _
                                  "values (" & EmpId & ", .....) "
                Dim myConnection2 As New OleDbConnection(strConn2)
                myConnection2.Open()
                Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
                myCommand2.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(Err.Number & " - " & ex.Message)
            End Try
           
            Dim myConnection2 As New OleDbConnection(strConn2)
            myConnection2.Open()
            Dim myCommand2 As New OleDbCommand(strSQL2, myConnection2)
            myCommand2.ExecuteNonQuery()
            myConnectio2.Close()
        End While

        myReader1.Close()
        myConnection1.Close()
        myCommand1.Dispose()
 
Upvote 0 Downvote

Similar threads

chacham
  • Question
Databind combobox to child table, show values from parent table
Replies
1
Views
449
chacham
chacham
T
  • Question
Attach Database to Project and View in Server Explorer
Replies
5
Views
2K
jmcilhinney
jmcilhinney
A
  • Solved
Resolved Enumerate network shares
Replies
5
Views
2K
aaaron
A
R
  • Question
Writing numbers to file and reading numbers from file
Replies
7
Views
452
realolman
R
U
  • Question
Getting file size during copy operation
Replies
2
Views
831
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top