how to set up a database connection with acces

[BrianHawley]

.NET will write the code for you.

You need to drop a data connector on to the form and then set its properties. There is a wizard to help with it. It will then create the code. Look at the code, and you will see how it is done. You can cut and paste it to use elsewhere, and just change the things you need, like the DB location etc.

The Help documentation and walkthroughs are aimed mainly at SQL Server (I guess MS is pushing it) but it's pretty much the same for any connection.

As I understand it (I'm still learning this), it is better to avoid ADO now, and use the recommended .net method as it is faster and more robust, and the data moves as XML, so it can travel more easily over the Internet, through firewalls that block binary traffic etc.

I'm writing a general purpose Access connector that I can call with just a database location and table name, but it's a long way from finished and full of bugs. Happy to send you a copy when it's done, unless you get there first, in which case I'd be interested to see your code!

[Marty Wijnen]

I'm very interrested in ur access connector. Could you plz email it to me? Thx.

ps. Do u know how difficult it is to connect to a foxpro free table? I just doesn't seem to get it to work.

[BrianHawley]

I would be happy to send it - but it's still a little way from being finished.

[Dillinger4]

As I understand it (I'm still learning this), it is better to avoid ADO now, and use the recommended .net method as it is faster and more robust, and the data moves as XML, so it can travel more easily over the Internet, through firewalls that block binary traffic etc.

Why would it be better to avoid ADO? Im just curious as to why you said that. Ive been progamming in VB/ACCESS for a while but with VB.Net looming overhead i havent used VB in over six months.

ADO should still function the same in VB.Net as it does in VB6. (Or at least i hope it does). So would this way to open up and connect to a database in VB.Net sill apply?

Option Explicit
Dim curRst As New ADODB.Recordset
Public cnnNet As New ADODB.Connection

Private Sub setinitialconnection()
Dim responce As String
Static flag As Boolean

' cnnNet.Open is for the initial connection
' if frmAccountSet up is closed and then reopened form another form
' we dont want to excute the following line of code again.

 If flag = False Then
   flag = True
    cnnNet.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & KeySetting
 End If
  curRst.Open "SELECT * From [Customers]", cnnNet, adOpenKeyset, adLockOptimistic
End Sub

[BrianHawley]

Originally posted by Dilenger4

Why would it be better to avoid ADO? Im just curious as to why you said that.

I'm just repeating something a Microsoft guy said at a recent seminar.

As I understand it (and I'm still wrestling with .net), you can still do most of the traditional VB stuff with .net, such as calling API's directly, usding ADO etc. but that this is mainly for backward compatability.

A 'pure' .net program does EVERYTHING through the .net framework. An 'impure' one does not.

This has certain implications. A 'pure' app can be installed just with xcopy (assuming the framework is installed on the target machine - which it will always be in future, since it will be installed along with the Windows operating system ). For a single file app, it is just a straight file copy. No DLLs, registration etc.

A 'pure' app will run unchaged on any operating system that has the framework installed - which in future may be ALL operating systems, if MS have their way.

An 'impure' app wil not enjoy these advantages.

At least that's what they told us.

[Cl()uD9]

You say the .net way to access data, what exactally is the .net way?

[BrianHawley]

It's using a disconnected dataset, with all the data moving as XML. You get the dataset you want off the server, but do not maintain the connection. You then work on the data locally, and only send updates back to the server if you need to.

This is a lot more effective than 'traditional' data connections where you open a connection to the server and hold it open for perhaps a long time, until you are finished with it.

It makes good sense for any data connection, but it is particularly good for things running across the Internet as the number of users may be large, and they may set up a connection but never come back.

Using XML is clever, since it can pass easily through firewalls and other things that do not allow binary data to pass across them.

.net still lets you do it the 'old' way if you want to, but you will lose the above advantages.