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]

Alrighty, here's some code to loop through your references to test for the existance of one of the Microsoft ActiveX Data Objects Libraries. If it's referenced, no need to try to establish a reference, if not, make the connection.<pre>
Sub WrkWRefs()
Dim n As Integer, p As String
For n = 1 To ThisWorkbook.VBProject.References.Count
p = ActiveWorkbook.VBProject.References.Item.Description
If InStr(p, "Microsoft ActiveX Data Objects") Then _
GoTo 1
Next n
ThisWorkbook.VBProject.References.AddFromGuid _
"{00000200-0000-0010-8000-00AA006D2EA4}", 1, 0
1: Call exportToAccessADO
End Sub</pre>
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-07 12:29

 

[KniteMare]

Nate,
I was thinking along the same lines, I also have not had time to play with it. I am working on a project for my BIG GIGANTIC I come first BOSS. (A great Guy) for a road trip he has to take to LA. (I offered to go myself but it seems he just has to do the presentation himself go figure ) Anyway I'll get back to it this week and repost my Success. or lack there of.

Ziggy,

What I need to do with this table is rather simple but the interface is in excel. I want to use the ACCESS Data base here (On the LAN) for my own stats each week, month year. I do not want the users to see it.

I have a nother much more complex program that I want to allow the users to archive complex information in ACCESS so that they cam retrieve complex historical queries. For Example to determine if an employee has called in sick 3 or more times in the last 90 days or 5 tiems in six months but to reset the query f the employee has already had their annual evaluation. These questions are best asked in ACCESS but the interface/ Forms are easier to design and retrieve input from in EXCEL so I have set up the program to present the front end in EXCEL. I aam now trying to get the Back End to run in access. Anny suggestions are greatly appreciated.

Yours in EXCELent Frustration
KniteMare

PS Nate, Someday, If your good I'll tell you where the "KniteMare" came from

 

[Centrican]

After much 'umm'ing an 'arr'ing I decided to give this method of updating an access database a go. I don't want to use it because it's massively long winded for updating 26 columns, but nobody seemed to know how to automatically "paste append".

The code appears to be working ok, only trouble is with the " Set InputRange" part.

My workbook is called master.xls and the worksheet is called "Data" and I can't work out how I would make sure the set inputrange is pointing at the right place.

Anyone able to shed any light?

 

[NateO]

Hello,

I'd change:

Sheets(1).[a1:a10]

To:

Worksheets("Data").Range("a1:a10")

But, perhaps post your current code, to see what you are working with...

Might make more sense to work with an array, etc... That might be faster than you might think it would, actually. Array->Recordset shouldn't be terribly slow...

 

[Centrican]

The actual code I'm using is below:

Sub exportToAccessADO()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cl As Range
Dim tablename As String
Dim InputRange As Range
Dim dbfullname As String
Dim FieldName As String

    dbfullname = "\\na-13\bgremicrosoftaccesslevel2\NationalMetering\ProActive_Compensation\aamanchesterreturns.mdb"
    tablename = "Issued Data1"
    
loops = 1

FN1 = "Week Ending"


    FN = "FN" & loops
    FieldName = FN

Sheets("data").Select
Selection.Activate

    Set InputRange = data.[a2:a100]
    
Run:
    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
    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

loops = loops + 1

    
End Sub

I cut out a few chunks of irrelevant code, is the "Inputrange" just wrong then?

 

[NateO]

It's wrong if the codename of the Worksheet, and I mean codename, is not 'data'.

Try my way of referencing the Worksheet.

 

[Centrican]

Thanks, Nate - that part appears to work fine now. But yet again it stumbles; for some reason it doesnt seem to like spaces in the names of tables or fields. It refused to find the table "Issued Data1" until I renamed it "Issued_Data1". Now it falls over each time I tell it which field to update. The field names again have spaces so I'm guessing this is why that is.

Is there any way to force it to pay attention to these spaces? I don't want to have to rename every table and field in all the queries this database contains.

 

[NateO]

You are welcome.

Quite right, put brackets around tables and fields with spaces, e.g., [Table Name].

Same way you would do in SQL, in Access. Always test your SQL in Access before you attempt to use it in a string, in VBA. Hmmm, am I allowed to use the word always? Perhaps, when in doubt, then...

 

[Centrican]

I'm getting closer to this now!

It now keeps over on the '.Fields([FieldName]) = cl.Value' line. the error is "Item cannot be found in the collection corresponding to the requested name or ordinal."

I see where it gets fieldname from; that's equal to my FN1 variable which is "[Week Ending]". This is the name of the field in the database so i cant imagine how it's wrong. The cl is equal (if I check) to a data value which is correct as week ending should be a date. I cant see what it's not finding... except perhaps the actual field?

 

[NateO]

Hello again,

Use brackets in the SQL string, not the Fields() Object reference, e.g.,

.Fields("FieldName")

[Anything] in Excel VBA is much different than it is in Jet SQL, you're inadvertently calling the Evaluate Method and this is causing your code to run amuck.