PDA

View Full Version : Solved: Push data to Access



Zack Barresse
08-07-2006, 10:21 AM
Hello all,

I'm trying to create an addin for Excel that will interface with Access and I'm not sure how to do it. I can get data from Access into Excel by Ken Puls method shown here (http://www.excelguru.ca/node/23); works beautifully.

What I ultimately want to be able to do, is populate multiple records and have a routine that will open the connection to the Access database, go through each record in the Excel sheet, check if it exists in Access, if not add it to the table (otherwise ignore*). I'm not even sure where to start on this one.

* It'd be nice to keep a list of all records, or at least a count, which did not get pushed to Access because of duplicate data.

NB: Duplicate data should be addressed by the Date field. Each Date field should have only one record per day.

Bob Phillips
08-07-2006, 10:59 AM
Writing to an Access table is just as easy using ADO.

This hould get you started



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


You can even create the database and the tables using ADO.

Zack Barresse
08-07-2006, 11:03 AM
Yes it will Bob, thanks. I almost had it, just had to tweak a couple of items. :)

OBP
08-07-2006, 11:58 AM
Zack, I see that you are still thinking Excel rather than Access to manipulate your data. You are using 30 to 40 lines of Excel code to do what Access does with 2 lines of code and a query. :dunno

Zack Barresse
08-07-2006, 12:46 PM
Hi Tony,

This is pretty much the only Excel-side there is to this database. It's because some people at my work will not lose Excel (and I cannot help that) completely, so the data they will be manipulating is still in Excel. I'm throwing this together for only the one aspect of the database to allow them to conjure all their data that they put together and after a few clicks, add it to the database.

I've got a userform right now which has four fields. One of the fields is a date field. This will be the field I need to use to check if that record already exists in the table. If not, I will insert a new record with all four fields.

The only problem I'm having now is populating the fourth field (a FieldID, as in crop field) as it's a foreign key and the FieldID does not match up to the Name field, which is what everyone is familiar with. Do you know of a way to check a table and for each value found, populate only one of the fields value into a combobox on a userform?

OBP
08-07-2006, 01:15 PM
If you set the date field in the Access table to Unique (No Duplicates) what happens to your code?
Are you talking of Excel checking the field or Access giving Excel the field?
To give excel the field from access just set your combo to a certain Excel range, link the range to a query that has your field in it.

Zack Barresse
08-07-2006, 01:24 PM
Right now I am populating the combobox when the userform is called with a database connection passing an SQL string and returning the values to a worksheet. I did it this way so that no matter what changes were made to the database, it'd be getting up-to-the-date information on every form load.

Oh, and btw, I was mistaken about the date (I'm sorry), there will be only one date per field entered. This is a little like the last one you saw Tony, but it's for the fields and not the fresh water meters.

OBP
08-07-2006, 01:30 PM
Are you still having problems with the foreign ID field?

Zack Barresse
08-07-2006, 01:33 PM
I'm trying to work out the SQL now. Once the form loads, I'll have the updated FieldID into a worksheet in the addin (it's a workbook now, but will be an addin when I'm done). I should be able to use that. Let me tie a couple things up and I'll upload a sample.

Zack Barresse
08-07-2006, 01:42 PM
File uploaded. The SQL for CheckRecord is not working right.

Bob Phillips
08-07-2006, 03:55 PM
File uploaded. The SQL for CheckRecord is not working right.

Can u post the db, and in what way is itr not working?

Zack Barresse
08-07-2006, 04:25 PM
I have gone home for the day, as this is a work project. I'll upload a zipped DB tomorrow. I've made some minor adjustments, so I'll re-upload the Excel file as well. Thanks both of you for taking the time to look at this. :)

Zack Barresse
08-08-2006, 09:15 AM
Well I'm having issues. I would upload a sample of the database, but somehow it's corrupted. It was working fine, then I went to compact and repair it and it froze halfway through comacting it. Now I can't open it, it says it's not a valid database and then it says it needs to repair it. I got it open once with data corruption (I can replace the data) but haven't got it open since. It keeps creating backup copies of it, each of those backups are corrupted as well. :(

mdmackillop
08-08-2006, 11:14 AM
My sympathies Zack.
In my databases at work, I keep all data in a "tables only" database and use links to the "Front Ends" that are on the user PCs. I've not suffered from data corruption, but have had to delete and replace the Front End with a new copy occassionally.

Zack Barresse
08-08-2006, 11:19 AM
That's what I'm working with too, I've split the database. This is my new Back End database where I was trying to restructure the tables, naming conventions, normalization and relationships somewhat in a decent fashion and semblance of the "right" way. I'll be bummed if I have to replicate all the work I've done last week and this week.