Automate Export of Data to an Access Table

[KniteMare]

I put together a little table for one of my LAN based facilities to track their Overtime Savings as compared to each of their individual units. It works great.

What I would like to do is to export the data on a weekly basis to Access. I set up an access table to recieve the data and I can get it all to work just fine except I want the users to just click on a menu item and have the export take place in the background.

I know how to create on the fly menus and get them to accecpt commands from Excel Macros so that is not an issue.

I know how to share data in Excel from Workbook to workbook, closed or open. I'm sure it can be done Excel to Access I just can not for the life of me find anything to jumpstart me in the right direction in either application's help files. Anybody out there linking their Excel data to Access?

Yours in EXCELent Frustration
KniteMare

 

[NateO]

You may want to check some of Ole's thoughts on the subject:

http://edc.bizhosting.com/english/adodao.htm

Rumor has it that ADO whups up on DAO.

Also, if you want different machines to utilize your upload, you'll probably want to programatically set up library references. Here's a thread with a few different ideas:

http://www.mrexcel.com/board/viewtopic.php?topic=10876&forum=2


HTH.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-25 09:58

 

[KniteMare]

Thanks Nate,
You da man!
I'll follow up on this right now.!

Yours in EXCELent Frustration
KniteMare

 

[NateO]

No problem Knight, I actually tweaked Ole P. Erlandsen's (hope he doesn't mind) ADO procedure, this may be easier to work with:<pre>
Sub exportToAccessADO()
'Ole P. Erlandsen's Code w/ Altercations
Dim cn As ADODB.Connection, rs As ADODB.Recordset, cl As Range
Dim tablename As String, InputRange As Range, dbfullname As String
Dim FieldName As String
dbfullname = "P:test.mdb"
tablename = "Table1"
FieldName = "blah"
Set InputRange = Sheets(1).[a1:a10]
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";"
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open tablename, cn, , , adCmdTable ' all records
For Each cl In InputRange
With rs
.AddNew
.Fields(FieldName) = cl.Value
.Update
End With
Next cl
Set rs = Nothing
Set cl = Nothing
cn.Close
Set cn = Nothing
End Sub</pre>

This works pretty well for me....

HTH.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-06-25 10:45

 

[KniteMare]

I grabbed your "Cleaned Up Version and exported it to my test bed. I am going to modify it to my file names and see how it works here. If this does what I think it does you are going to make me look GREAT. I hope sometime I can return the favor!!

I have a scheduling program I use here that they have been asking me to link to a Database for the last year or so. If I can taylor this to work with that MUCH!!! MORE!!! COMPLEX!!! Excel Program I'll be a happy camper.

Thanks again Nate.
Yours in EXCELent Frustration
KniteMare

 

[NateO]

You're welcome Knite! I may have to take you up on that favor some day.

Not sure if you were able to set up your referencing or not, I set up a procedure which the procedure above runs through. I had to separate them them as if the reference isn't set, XL jumps to the dim statements in the procedure above and bonks out. I just went for the lowest version, with some [perhaps too] simple error trapping:<pre>
Sub Reference_Active()
Dim ID As Object
On Error GoTo 1
Set ID = ThisWorkbook.VBProject.References
ID.AddFromGuid "{00000200-0000-0010-8000-00AA006D2EA4}", 1, 0
Call exportToAccessADO
Exit Sub
1: MsgBox "Error establishing VBE library reference to" & _
"MS ActiveX Data Objects." & Chr(13) & Chr(13) & _
"Please establish library reference manualluy (Call KniteMare)."
End Sub</pre>

Thought I'd throw it out there for the halibut. Nice code from a while back Ivan (if you spot this).


_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-09-13 14:31

 

[KniteMare]

I set the Reference to MSDataObjects 2.0 lib and the code runs. (Other choices were 2.1 and 2.5, I do not know the difference between these libraries so it is off to the books again to learn!) I had to block the "exportToAccessADO" to get this to work. Then un-block it. Otherwise Compile Error stating "User Defined Type Not Defined".

Anyway I have at least started on the path to linking EXCEL to ACCESS, thanks in no small part to your guidance!. I have however not had the time to continue. My partner and I had to go to each of our 18 facilities yesterday and make a minor change with major consequences. I wanted to make the change myself so that I could do the staticical analysis for the BIG bosses and be sure my data was correct. I'll be back to playing with this on Monday or Tuesday. Tnx Again.

BTW I did find some time to run this again. I am getting the data to the ACCESS file and the correct table but all data is going into column one of the ACCESS table, I think I have to reference .Fields(FieldName) = cl.Value differently but I'm not yet sure. I have to play with it some more to see what I am doing wrong. Believe it or not this is the best part of codeing, that is figuring out how it works and fighting it like a fish to get it to work right.


Yours in EXCELent Frustration
KniteMare
This message was edited by KniteMare on 2002-06-27 12:22
This message was edited by KniteMare on 2002-06-27 12:27

 

[davers5]

Nate and KniteMare and others--

Thanks a bunch for this chunk of code! I got it to work and it's brilliant. However, there is still some stuff I don't understand and I was wondering if somebody could help me understand better. Specifically, what are library references and how do they work? Is it as simple as pointing Excel to a place so that it understands your code? If so, why aren't all the referneces automatically loaded? Is there a simple explanation to this or can anybody point me to some reading I can do on the subject?

Thanks,

Dave S.

 

[NateO]

Howdy y'all,

Knite, all of the cells in the input range should go to the particular Access field you specified. There are a few other ways I can think of doing it:

1) Do different loops for different ranges, and different fields

2) Set up arrays for both the pull (input range) and the push (Access table field). I haven't played with this (been on the road myself).

With Libraries, they are references to Excel add-ins which come with Excel which define terms for you, e.g., ADODB.Recordset

It probably would be nice if Excel did self-load the appropriate library, alas, it doesn't, hence the need to do so.

It's probably a good idea to loop through the established references to see if the appropriate one exists, by a common denominator. Excel is fine if you try to establish a reference to a library that's set-up, but trying to load different versions of the same type is problematic. So, if one is already set, no need to beat the dead horse as they say.

Glad the code is of interest. Will look into the library test (when I'm not at a thin client).

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-07-03 19:13

 

[Ziggy]

Hey Knitemare & others,

I use a (personal)macro to save/refresh my data to a particular excel file(s). The macros read data from the main computer via ODBC drivers through MS query and then save in Excel format.

The Excel file(s) are linked to My Access data base. The link is established via access, you could also automate importing the table into Access using Access Macro's and Append queries.

Users run the Macro in Excel but you can code it to run the (Excel)macro to run from Access.

To me it is the simplest way but I don't know exactly how your process works, just thought I'd let you know how I use linked Excel/Access.

Good luck