if you know any good sources about OleDB

[romabr]

Hi ,
I am started to work with OleDb objects (VB.net) .
Please ,let me know any good free web resources about :
"How to work with access DataBase from vb.net environment"

the questions are :
1. i am working with windows application and trying to create new access db and copy (duplicate) existing access db with different name to some directory .
2. i am need to create archive : cheking existing archiv.mdb and update him with new rows from source.mdb ,then delete all rows that older then 2 monthes from source.mdb . This program must repeated once in all month .

Thanks

PS. when i will finish this task , i can post there my solution (if anyone interested of course)

 

[jmcilhinney]

You would need to use ADOX to create an Access database in code from scratch. Once the database exists you can use ADO.NET to do some things but there are certain operations that will need ADOX to complete.

 

[TechGnome]

Can ADOX create an Access database from scratch? Or does the mdb already need to exist? I know ADOX can create tables and all that, I'm just curious about the initial creation of the db file itself.

-tg

 

[jmcilhinney]

I've never used ADOX myself but, from what I've read, I was under the impression that it could create a Access database from thin air. Perhaps I'm wrong and I was just assuming.

 

[romabr]

Oh ,no .
It is not so complicated .
I have source.mdb (this file contain all data base) , and I need to rename this file and copy it to same location . At end it will be 2 access files in same directory : source.mdb and archiv.mdb . They are equal , just have different names .
How I can do it plz ?

 

[jmcilhinney]

Would not the obvious thing to do just be to copy the file using IO.File.Copy and then just delete all the data if you want it empty? Just note that if you are using tables with autogenerated key values then you will not be able to just transfer the data from one table to another table with the same schema and keep the same key value. You would have to change the schema of the table in the archive table so that it used integers but not autogenerated ones, so that you could transfer the records without having a new key generated for them in the archive table. You may be able to use ADO.NET to change the column properties (OleDbCommand.ExecuteNonQuery) but I'm not sure. You may have to use ADOX for that.

 

[romabr]

Thanks to all .
The first part is comlete .
This VB.NET code checks if arch.mdb already exist . If not - creates new arch.mdb and delete all rows from all tables .

' this function execute sql non query command - str
ProtectedSub ExecuteCommand(ByVal c As System.Data.OleDb.OleDbCommand, ByVal str AsString)
c.CommandText = str
c.ExecuteNonQuery()
EndSub

PrivateSub btnMoveDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveDB.Click
Try
Dim f As IO.File
If f.Exists("d:\Arch.mdb") Then
MsgBox("Found Arch.mdb ,all archive information will be added to this DataBase.")
Else
f.Copy("d:\Source.mdb", "d:\Arch.mdb")
MsgBox("Created new archive file - Arch.mdb")
EndIf

ArchConnection.Open()

Dim strSqlDelete As String
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = ArchConnection

strSqlDelete = "delete * from events "
ExecuteCommand(cmd, strSqlDelete)
strSqlDelete = "delete * from schedual "
ExecuteCommand(cmd, strSqlDelete)
strSqlDelete = "delete * from users "
ExecuteCommand(cmd, strSqlDelete)

MsgBox("You have succesfull deleted all information from Arch.mdb")

Catch ex As Exception
Dim errStr AsString
errStr = ex.ToString
MsgBox(errStr)
MsgBox(" Error !!! ")
Finally
ArchConnection.Close()
EndTry
EndSub

It is all . If you have any comments about code i will thanksfull .

Now I have 2 mdb files . Do you have any ideas how to update arch.mdb with rows from source.mdb ?
Actually i can manage archive tables in same data base , but there i need to compare tables from 2 different data bases . How to do this ?

 

[jmcilhinney]

In order to transfer records from the current database to the archive database, here's the method I would use:

1. Create an OleDbDataAdapter with a SelectCommand that will retrieve all the records that meet the criteria for being archived. Make sure you set the AcceptChangesDuringFill property to False.
2. Create an OleDbConnection to the current database and assign that to the Connection property of the SelectCommand.
3. Fill a DataTable using the DataAdapter. Because the AcceptChangesDuringFill property is False, every DataRow will have a RowState of Added, which means that they are ready to be inserted.
4. Create an OleDbCommand object to insert the rows into the archive database and assign that to the InsertCommand of the DataAdapter.
5. Create an OleDbConnection to the archive database and assign that to the Connection property of the InsertCommand.
6. Call Update on the DataAdapter and pass the DataTable to insert all the records into the archive database. This will also implicitly call AcceptChanges so all DataRows will have a RowState of Unchanged.
7. Set the InsertCommand to Nothing.
8. Use a For Each loop to iterate over all the rows in the DataTable and call Delete on them, which will set the RowState to Deleted.
9. Create an OleDbCommand object to delete the rows from the current database, assign the existing Connection for the current database to its Connection property and assign the Command to the DataAdapter's DeleteCommand property.
10. Call Update on the DataAdapter and pass the DataTable to delete all the records from the current database.

I would also recommend that you use transactions so that you can Rollback the insertions if the deletions fail for some reason. If you need code for this, let me know and I'll whip some up. It's actually not all that complex, but if you are a little unsure with ADO.NET then I'm happy to provide more detail.

 

[romabr]

Sorry , i was sick about few days :-(
Now i was try to insert my dataset into arch.mdb (unsuccess)
then i was try to insert datatable as you write (unsuccess)
i use transaction ,but it still not work .

i will thankful if you send me a part of your code ,where you insert datatable into access table .

romabr76@yahoo.com