PDA

View Full Version : Solved: DAO access from excel



gibbo1715
10-18-2005, 01:56 PM
All

I ve attached a spreadsheet that will create an access database from a userform in excel using DAO, its no where near finished but has a lot of stuff in it that I wanted to share now as I hoped others would find it useful too.

I do also have a question, the code below is used to populate the textboxes using data from an access database, my problem is if the value is null then it just leaves the previous data in the textbox.

Also working the other way, if I leave a textbox blank and try to update my record in my dataset I get an error, I have found a crude way around this problem using an if statement as below but am sure there must be a better way, anyone got any ideas?

Please feel free to play with the attachment and amend it to make it as useful as possible for everyone here.

Thanks to XLDennis and XLD for getting me interested in this in the first place

Gibbo

Private Sub Navigation()
On Error Resume Next
If rstObj!Text <> "" Then
TextBox1.Text = rstObj!Text
Else
TextBox1.Text = ""
End If
TextBox2.Text = rstObj!Integer
TextBox3.Text = rstObj!Long
TextBox4.Text = rstObj!Double
TextBox5.Text = rstObj!Boolean
TextBox6.Text = rstObj!Memo
If rstObj!Currency <> "" Then
TextBox7.Text = rstObj!Currency
Else
TextBox7.Text = ""
End If
TextBox8.Text = rstObj!Date
End Sub

Bob Phillips
10-18-2005, 02:11 PM
Thanks to XLDennis and XLD for getting me interested in this in the first place

Don't blame me for you getting you interested in DAO. I'm an ADO believer, I don't trust any of those mystic religionshttp://vbaexpress.com/forum/images/smilies/107.gif

gibbo1715
10-18-2005, 02:21 PM
True you were helping me with the ADO approach earlier which I do appreciate

To be fair i was gonna go with ADO until I found my works computers could only ref DAO, so I thought id have a look at both methods

Gibbo

Bob Phillips
10-18-2005, 02:29 PM
True you were helping me with the ADO approach earlier which I do appreciate

To be fair i was gonna go with ADO until I found my works computers could only ref DAO, so I thought id have a look at both methods

Only kidding http://vbaexpress.com/forum/images/smilies/devil.gif.

How do you NOT have ADO?

gibbo1715
10-18-2005, 02:31 PM
How do you NOT have ADO?

Dont really know, i have excel 2000 at home and work but the ref was missing on my work machine and no other ADO ref to link too

very strange

Gibbo

Bob Phillips
10-18-2005, 02:38 PM
Dont really know, i have excel 2000 at home and work but the ref was missing on my work machine and no other ADO ref to link too.

Ah, that might be subtly different. You might well have ADO on the system, but your company's installation might remove the reference som ehow. I just couldn't figure out hgow you could not have ADO, as I thought it get installed with so many products.

If you were interested, you could do a search for msado*.tlb or msado*.dll on your work's machine.

gibbo1715
10-18-2005, 02:45 PM
will try that tomorrow, cheers

One other question about ADO whilst were at it

is there an easy way to edit a record using ADO?

I can use

rstObj.Edit
rstObj!Text = TextBox1.Text
rstObj.Update

using DAO but this is not available in ADO

Is there a similar method available through ADO

Cheers

Gibbo

gibbo1715
10-19-2005, 01:46 AM
Can i return this to my original question then please if no one can help me with editing a record using ADO

Cheers

Gibbo

I do also have a question, the code below is used to populate the textboxes using data from an access database, my problem is if the value is null then it just leaves the previous data in the textbox.

Also working the other way, if I leave a textbox blank and try to update my record in my dataset I get an error, I have found a crude way around this problem using an if statement as below but am sure there must be a better way, anyone got any ideas?

Bob Phillips
10-19-2005, 02:33 AM
Can i return this to my original question then please if no one can help me with editing a record using ADO

I haven't tried it as I am not about to get into DAO with my bias http://vbaexpress.com/forum/images/smilies/devil.gif, but have you tried using IsNull againats the field. That should work extracting the data, but personally, I don't allow NULLs in my databases, far too many problems with them.

gibbo1715
10-19-2005, 02:42 AM
Thanks XLD

Have to admit i do prefer ADO and would prefer to stick with that method,

I still have the problem of being able to edit a record though using ADO and this seems to be quite problomatic, I do need to be able to do this so would be grateful of an example of how to do this

Or what about using an SQL Query to edit/append a record then, is that perhaps a better method?

Again any chance of an example or a place to look

Cheers

Gibbo

Bob Phillips
10-19-2005, 02:53 AM
I still have the problem of being able to edit a record though using ADO and this seems to be quite problomatic, I do need to be able to do this so would be grateful of an example of how to do this

Or what about using an SQL Query to edit/append a record then, is that perhaps a better method?

Gibbo,

I use SQL to update/add data personally.

Are you asking for an example of updating and adding data to a database using ADO?

gibbo1715
10-19-2005, 03:00 AM
I think I would like to learn to do it via SQL as a preference

but an example of how to edit a record using both methods would be great if possible so I can start to get my head around both methods

If not an example of either method would be appreciated

Cheers

Gibbo

Bob Phillips
10-19-2005, 04:10 AM
I think I would like to learn to do it via SQL as a preference

but an example of how to edit a record using both methods would be great if possible so I can start to get my head around both methods

If not an example of either method would be appreciated


Gibbo,

Here are some examples of using ADO and SQL with an Access database. ADO also supports that Edit construct that you used, but when I tried it with the Access database, it didn't work. It might just be Access, it might be the Jet OLEDB provider (maybe it works with ODBC), but I am not an Access user normally, and I prefer OLEDB providers if possible.

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE Contacts " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From Contacts"
oRS.ActiveConnection.Execute sSQL
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If


oRS.Close
Set oRS = Nothing
End Sub

gibbo1715
10-19-2005, 05:43 AM
XLD Thanks so much,

much appreciated,

Interesting Phone number as its from my area in the UK

Cheers

Gibbo

Bob Phillips
10-19-2005, 06:10 AM
Interesting Phone number as its from my area in the UK

Poole or Bournemouth?

gibbo1715
10-19-2005, 08:13 AM
On the edge of Bournemouth (Wallisdown)