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(sOURC E,DESTINATION)" .
Eg: if the path is "c:\Programs\da ta.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.
4 1535
i tried this code....
Code
--------
Dim nPath As String = "\\t1\root\Prog rams"
Dim strConn1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & nPath & "\DataTables.md b"
Dim strSQL1 As String = "Select * from Employees"
Dim myConnection1 As New OleDbConnection (strConn1)
myConnection1.O pen()
Dim myCommand1 As New OleDbCommand(st rSQL1, myConnection1)
Dim myReader1 As OleDbDataReader = myCommand1.Exec uteReader
While myReader1.Read
Dim nPath2 As String = "\\t2\root\Prog rams"
Dim strConn2 As String = "Provider=Micro soft.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.O pen()
Dim myCommand2 As New OleDbCommand(st rSQL2, myConnection2)
myCommand2.Exec uteNonQuery()
End While
myReader1.Close ()
myConnection1.C lose()
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.Invalid CastException' occurred in microsoft.visua lbasic.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.
Code:
Dim nPath As String = "\\t1\root\Prog rams"
Dim strConn1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & nPath & "\DataTables.md b"
Dim strSQL1 As String = "Select * from Emp"
Dim myConnection1 As New OleDbConnection (strConn1)
myConnection1.O pen()
Dim myCommand1 As New OleDbCommand(st rSQL1, myConnection1)
Dim myReader1 As OleDbDataReader = myCommand1.Exec uteReader
While myReader1.Read
Dim nPath2 As String = "t2\\root\Progr ams"
Dim strConn2 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & nPath2 & "\DataTable.mdb "
Dim strSQL2 As String = "insert into Emp (EmployeeID, EmployeePrivate AccessNumber,Sw ipeCardNumber,S ocialInsuranceN umber, FirstName, LastName, Title, Address, City, State, PostalCode, HomePhone, EmrgcyContactNa me, EmrgcyContactPh one, IsThisPersonAMa nager, OpenWhichCashDr awer, AccessToManager Screen, AllowedToGiveDi scounts, AllowedToDelete SentItems, AllowedToCompIt ems, AllowedToCashOu t, AllowedToAccess OtherOrders, AllowedToChange Items, AllowedToRunRep orts, AllowedToQuickC ashOut, WhichMenuLevel, index, StartOnQuickScr een, HourlyWage, AllowedToTransf erOrders, Dancer, StageName, Age, DepartmentID, ListStyle, AccessToServerS ide, Driver, AccessToPrintSp ooler, BusBoy, WhichRoom, AccessToHouseAc counts, ShareTips, JobCode1, JobCode2, JobCode3, JobCode4, JobCode5, JobCode6, JobCode7, JobCode8, JobCode9, JobCode10, JobCode1HourlyW age, JobCode2HourlyW age, JobCode3HourlyW age, JobCode4HourlyW age, JobCode5HourlyW age, JobCode6HourlyW age, JobCode7HourlyW age, JobCode8HourlyW age, JobCode9HourlyW age, JobCode10Hourly Wage, Message, Cashier, TipOutToBarStaf fOnLogOut, TipOutToKitchen StaffOnLogOut, AllowedToEmail, MultiSelectOnMa inMenuList, RecordLogOutInf o, TrainingMode, InActive, Salaried, DeliveryDispatc her, ForceDrawer1_2, JobCodeProfile, CompValue, CashLimit, ServerKey, ClearviewKey, LanguagePrefere nce, 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.O pen()
Dim myCommand2 As New OleDbCommand(st rSQL2, myConnection2)
myCommand2.Exec uteNonQuery()
End While
myReader1.Close ()
myConnection1.C lose()
myCommand1.Disp ose()
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.Exec uteNonQuery() line error occurs as below.
Error
-----
An unhandled exception of type 'System.Data.Ol eDb.OleDbExcept ion' 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...
and i insert the TRy Catch in my codes.
Code:
Dim nPath As String = "\\t1\root\Prog rams"
Dim strConn1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & nPath & "\DataTables.md b"
Dim strSQL1 As String = "Select * from Emp"
Dim myConnection1 As New OleDbConnection (strConn1)
myConnection1.O pen()
Dim myCommand1 As New OleDbCommand(st rSQL1, myConnection1)
Dim myReader1 As OleDbDataReader = myCommand1.Exec uteReader
While myReader1.Read
Dim nPath2 As String = "t2\\root\Progr ams"
Dim strConn2 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & nPath2 & "\DataTable.mdb "
Try
Dim strSQL2 As String = "insert into Emp (EmployeeID, EmployeePrivate AccessNumber,Sw ipeCardNumber,S ocialInsuranceN umber, FirstName, LastName, Title, Address, City, State, PostalCode, HomePhone, EmrgcyContactNa me, EmrgcyContactPh one, IsThisPersonAMa nager,OpenWhich CashDrawer, AccessToManager Screen, AllowedToGiveDi scounts, AllowedToDelete SentItems, AllowedToCompIt ems, AllowedToCashOu t, AllowedToAccess OtherOrders, AllowedToChange Items, AllowedToRunRep orts, AllowedToQuickC ashOut, WhichMenuLevel, StartOnQuickScr een, HourlyWage, AllowedToTransf erOrders, Dancer, StageName, Age, DepartmentID, ListStyle, AccessToServerS ide, Driver, AccessToPrintSp ooler, BusBoy, WhichRoom, AccessToHouseAc counts, ShareTips, JobCode1, JobCode2, JobCode3, JobCode4, JobCode5, JobCode6, JobCode7, JobCode8, JobCode9, JobCode10, JobCode1HourlyW age, JobCode2HourlyW age, JobCode3HourlyW age, JobCode4HourlyW age, JobCode5HourlyW age, JobCode6HourlyW age, JobCode7HourlyW age, JobCode8HourlyW age, JobCode9HourlyW age, JobCode10Hourly Wage, Message, Cashier, TipOutToBarStaf fOnLogOut, TipOutToKitchen StaffOnLogOut, AllowedToEmail, MultiSelectOnMa inMenuList, RecordLogOutInf o, TrainingMode, InActive, Salaried, DeliveryDispatc her, ForceDrawer1_2, JobCodeProfile, CompValue, CashLimit, ServerKey, ClearviewKey, LanguagePrefere nce, AccessLevel) " & _
"values (" & EmpId & ", " & AccessNo & ", " & Cardno & ", '" & SINno & "', '" & FName & "', '" & LName & "', '" & eTitle & "','" & eAddress & "', '" & eCity & "', '" & eState & "', '" & ePin & "', '" & eHomePhone & "', '" & eEmyContactName & "', '" & eEmyContactPhon e & "', " & 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.O pen()
Dim myCommand2 As New OleDbCommand(st rSQL2, myConnection2)
myCommand2.Exec uteNonQuery()
Catch ex As Exception
MessageBox.Show (Err.Number & " - " & ex.Message)
End Try
Dim myConnection2 As New OleDbConnection (strConn2)
myConnection2.O pen()
Dim myCommand2 As New OleDbCommand(st rSQL2, myConnection2)
myCommand2.Exec uteNonQuery()
End While
myReader1.Close ()
myConnection1.C lose()
myCommand1.Disp ose()
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'."
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.
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..... -
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()
-
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ove |
last post by:
I have one Access 2000 DB on my website. I want to access these tables
from my local database so i do not need to down-/upload the database
each time i want to make changes.
Meanwhile i want a external partner to access (just read) another
database on my web.
I have tried with "link tables" but do not succeed. Help please!
/Ove H,
|
by: GiladP1 |
last post by:
Hi,
I want to copy protect my application. I want to limit its use for a
given period of time, say 30 days, after which a license key will be
needed.
There are various issues to consider here like:
1)
How to keep the installation date on the user's computer so that a
simple uninstall and a complete deletion of the application's directory
|
by: Ram Baruch |
last post by:
Hi,
I'm trying to use the File.Copy() function. It works well when the
desenation file is local (Like: C:\dest\dest.exe). The problem is that when
I'm trying to copy to a destenation that starts with '\\' it doesnt work and
exception is thrown (for example: \\MyComputer\DestDirectory). Even if the
first and the second directories are actually...
|
by: adnan boz |
last post by:
Hi All,
Does somebody know why File.Copy function is 10 times slower than standard
windows drag and drop copy between folders, when I copy files over the
network?
Actually, I need to copy files from one computer to another over a 1GBit
ethernet network as fast as I can. Although windows copy works at %40
performance, File.Copy could not go...
|
by: Paul Brady |
last post by:
I volunteer at a youth ministry agency and help them with their
student database. They have two computers, both running Windows XP.
Both have Office 2002 installed without Access, except that Computer A
had A97. They wanted both computers to be able to use the database,
stored on Computer A. I can't get them to do that, and it seems to be...
| |
by: =?Utf-8?B?U2Vhbk1hYw==?= |
last post by:
How do I programmatically copy files across a network (from a local
workstation to another local workstation on the network) using visual basic
2005?
In other words, when in explorer and you enter a unc path to a network
workstation, you are prompted with a dialog box asking for the user name and
password that has access rights to that...
|
by: Andy_Khosravi |
last post by:
I just recently changed my database that I'm running from a monolithic
DB to a split FE/BE. The front end resides on the client machine and
the BE resides on a network drive. I'm experimenting with a utility
developed by Tony Toews to handle the distribution and subsequent
updates of the software. I'm having some trouble with the overall...
|
by: rizwan6feb |
last post by:
Hello everybody. I need help on how to use copy function to copy file from one computer to another ( or any other way i can copy the file). The computers are on a local network.
|
by: Jason |
last post by:
I want to create a simple program with Two buttons on the form.
BUTTON 1 - BACKUP PREFS
this will do the following:
Copy C:\Documents and Settings\%USERNAME%\Application Data\FileZilla
\sitemanager.xml
to this location: \\drake\pvt\%USERNAME%\FileZilla-Prefs\
BUTTON 2 - RESTORE PREFS
this will do the opposite:
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |