Need help with coding

Gini · July 1st, 2008, 04:49 AM

I want to create a code in vb.net 2008 that will delete data from its database table(Microsoft Access). The columns (attributes) in the database table are CWNo, Name, IC No, Nationality, Company, Join Date and Expiry Date.
Then it will insert data from a Microsoft Excel Sheet that contains the same attributes above into the same access database table.

Can you please give me a hint on how I can do this? Maybe you can provide me with an algorithm or even code that will be useful for me to complete the task above.

planoie · July 1st, 2008, 01:32 PM

You've asked a pretty broad question and provided nothing to inform us of what you do or do not know. If you are a beginner, then you should probably start with simple tutorials and/or a book. There are plenty out there.

-Peter
compiledthoughts.com

berridge · March 6th, 2009, 04:14 PM

Hi, I spent a while trying to figure this out and assumed there would be an easy way to move data between Access and VB .NET. In the end I got the following to work. It is a little unwieldy, but it basically returns a query result as a collection with arrays for the field names and the record data separately:

Code:

PrivateSub Button1_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Button1.Click
Dim queryString AsString = "SELECT * FROM [table name];"
Dim callsigns AsNew Collection
callsigns = query_database(queryString, )
 
EndSub
PrivateFunction query_database(ByVal query_string AsString, OptionalByVal connect_string AsString _
= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\yourdatabase.accdb") As Collection
Dim query_result AsNew Collection
Dim row_count AsInteger = 0
Dim field_count AsInteger
Dim loop1 AsInteger
 
Using dbConnection AsNew OleDb.OleDbConnection(connect_string)
Dim cmd AsNew OleDb.OleDbCommand(query_string, dbConnection)
dbConnection.Open()
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
field_count = reader.FieldCount
Dim labels(field_count - 1) AsString
For loop1 = 0 To field_count - 1
labels(loop1) = reader.GetName(loop1)
Next loop1
query_result.Add(labels)
Dim table(field_count - 1, 0) AsObject
While reader.Read()
ReDimPreserve table(field_count - 1, row_count)
For loop1 = 0 To field_count - 1
table(loop1, row_count) = reader(loop1)
Next loop1
row_count += 1
EndWhile
query_result.Add(table)
dbConnection.Close()
EndUsing
query_database = query_result
EndFunction

Hope you find it useful, albeit a little late! It probably works for delete type queries as well although I haven't tried it.

Cheers,
Lloyd.