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]

Sure, you just need to change your connection string. See the following:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

 

[guyborchers]

Nods, NateO...

I ended up finding some information in a broader search through google which I was then able to piece together...

but this information in the link was exactly what I was needing in order to have a starting point.

Thanks,

~Guy

 

[NateO]

You are welcome.

Pretty powerful stuff, eh?

 

[guyborchers]

Yes indeed,

I was surprised at how little information I could pull up on the search of these forums about MSSQL.

Plenty could be found about Access, but I guess since I was not too fluent in SQL and unfamiliar with ADO the direct translation of how to go from A to B was lost on me.


I was also quite surprised at how quickly Excel could handle multiple queries over a network connection to the Database server etc.

something like .25 seconds for 36 select and 36 insert commands.

of course, I did not really optimize my script to avoid queries where possible, but just the fact it was quick enough for my purposes to begin with was promising.

 

[NateO]

There should be a fair amount of overhead in opening a Recordset so be careful how you do that...

You might be better off opening a larger Recordset and filtering that, if need be, versus recreating Recordset Objects and destroying them, over and over again.

Here's a little example of doing this:

http://www.utteraccess.com/forums/showthreaded.php?Number=1412972

And a monsterous thread on doing this in Access:

http://www.utteraccess.com/forums/showthreaded.php?Number=1423318

Or, are you just executing T-SQL?

Using VBA really starts to throttle MS Query when it comes to cutting Recordsets in a different ways in one go because you can do it with a single Connection. MS Query requires seperate Connections for each QueryTable Object... Slow.

Make sure you're doing all of this within one Connection, connecting has associated overhead.

 

[NateO]

P.S. - This is why Microsoft migrated their Jet Database focus from DAO to ADO with Access 2000. Other than the Connection String and SQL dialect, neither of which are trivial, btw, it's all the same ADO Object model.

And, it's powerful. Thank you, Microsoft.

 

[bj6671]

I'm fairly new to this, but I think I almost have it down.

What I have going on is a database that needs to pull an Excel spreadsheet off a web link and put it in a Table (almost exactly what you all are talking about) keeping the Headers as Fields.

So I have Access open Excel and run a macro that pulls down the spreadsheet (with headers), manipulates a few cells, deletes some columns, deletes some rows, and generally puts it into the format I want.

I was looking for a way to move the data from Excel to Access, all in one big chunk, without saving it to the hdd first.

I wanted to do this with a command like

strSQL = "INSERT INTO " & strTblName & " SELECT * FROM ..."
adoCN.Execute (strSQL)

However, I can't get my FROM statement to work.

The example I was working off of uses this format:

stSQL = "INSERT INTO Table1 SELECT * FROM [Sheet1$] IN '" _
& ThisWorkbook.FullName & "' 'Excel 8.0;'"

This format doesn't work for me for two reasons.
One: The Macro that is running is in a different workbook then the data, so ThisWorkbook.FullName grabs the wrong file.
Two: I didn't want to save the file, so it doesn't exist on the HDD for this line to reference.

There should be some way that I can reference an open, unsaved worksheet in that FROM clause, but I don't know what it is.

-------------------
Nevermind!

I scrapped the SQL idea and went for nestled For/Next loops with an array for the field names. It's working well enough.

 

[NateO]

Hello,

Why not use Automation, then? E.g.,

http://www.mrexcel.com/board2/viewtopic.php?p=1232569#1232569

 

[bj6671]

NateO,

Except for the LBound and UBound stuff (I used about the same thing, but found another way), that's almost exactly what I did.

I was afraid it would take too long, but it goes so fast, even with a couple thousand records. I demo'd my import method compared to the old method (which involved saving the Excel spreadsheet to a network drive then having Access import it back over the network) and they were impressed at how much faster it was.

Working on the other details now, but thanks for answering!

 

[Barkeep]

I'm attempting to export a line of data into an access table, but the code I'm using is not working out. Can anyone spot my problem?

Thanks!

(my data range I want export out is "AccessPaste1")

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 = "M:\History.mdb"
    tablename = "History Table"

    Set InputRange = Sheets(1).[AccessPaste1]
    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

    Set rs = Nothing
    Set cl = Nothing
    cn.Close
    Set cn = Nothing

End Sub