Updating Access table with Excel information

[Mortalous]

Hello all. I have a little problem that I need to get resolved. Basically I have an excel spreedsheet that users enter data on. I want to be able to have them enter a value into a cell then hit the enter key or click a button and from that, it appends an Access table with the date and time of the entry along with the data. Also, once they enter this data I want it to append to the table and clear the cells out again. Currently, I have everything completed date and time stamp is automatically generated on the excel sheet) except for the linking I spoke of above. I was told that I can use DDE to accomplish this but I truly have no experience with coding DDE or VB for that matter. Is there an easy way to do this? Anyone have any resources that can help me do this on my own? Please offer an advise you may have. Thank you all . .

 

[PaulBricker]

Well the first logical question is why aren't you using an Access Form to enter the info instead of a Spreadsheet? Is that even an option

Paul

 

[Mortalous]

Unfortunately, I have already posed this question and my supervisor wants it to be done this way. So, I am trying to find out how to code it to be done this way. Basically, the excel spreedsheet is distributed to the factory and once a part is bad or returned they enter it into the excel spreedsheet. Then, on a weekly basis, there are 3 users who organize the data with Access and do the data manipulation there. I hope this helps explain the whole process better. . Thanks for your help

 

[hysteria]

I dunno if this will help but this code can be used to export information from an excel worksheet into an Access database table.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0 ' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1&quot = Range("A" & r).Value
.Fields("FieldName2&quot = Range("B" & r).Value
.Fields("FieldNameN&quot = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


You can have a button on the worksheet and associate this macro with it.

hope it helps.
Have fun.

 

[Mortalous]

Thank you for writing that code out. Since I know nothin about it, I was wondering if you could help with one other thing (ok 2 things, lol). First, I have created a macro with this code, and then assigned this to a button. Once I click on the button it gives an error messgae back that says, "(Dim cn As ADODB.Connection is highlighted) user-defined type is not defined." I always thought the Dim statement was defining the variable and ifnot, I don't know how to correct this. Secondly, is there an easy way to make it so it will erase the data that is entered into the excel fields once everything is done? (the entering of the data into the table and then clear the original data from the excel spreedsheet)

Thank you for your tremendous help!

 

[hysteria]

No Problem at all.Amazingly I had been working on a similar thing just last week..heh..
Ok the first error is because some project libraries are not referenced.Certain Objects before use have to be referenced.Anyways
When you get the error,press end.
Then select tools->references in the visual basic editor(VBE).
Over here you would need to check a few libraries.I do not remeber all that will be required but will give it a shot.
Microsoft Access 8.0 Objects Library
M ActiveX Data Objects (multi dim)
ActiveX conference Control
M ActiveX dataObjects 2.5 Lib
M ActiveX plugin
M ActiveX DataObjects Recordset 2.0
M ADO Ext 2.5 for DDL and Security
M Jet and Replication Objects 2.5 Lib
M OLEDB service component 1.0

check all of these...it should work then.

One easy way to write code for something that you can do manually is to simply record a macro.For deleting that code I would suggest you record a macro and then copy it below the code previously written so that after adding all the information to the table it is deleted from the excel worksheet.To record a macro, in the main excel window go to tools->macro->record a macro.
Then select the range with ur mouse that has to be deleted and then delete that.This will be transformed to code because its being recorded.Then you can stop the macro and view it in visual basic editor, a new module would have been added and that can be seen under modules at the left hand side of ur VBE window.Copy it from here and add it at the end of the previous code.
Actually I should give you some code here as well..heh...if that doesent work...(it really should) try this out.

Dim myRange As range
sheets("sheetName&quot.select
Set myRange=ActiveSheet.Range("A10:B20&quot 'select a range
myRange.clearContents ' or myRange.delete try either

I hope it works
seeya

 

[Mortalous]

Man you guys and gals are wonderful! However, it seems the code still will not work, and I really wish I knew why and how to correct it myself without asking for help. Anyways, I will try the code and/or the maacro recording for deleting, but first I need to try and get the first part to work. The error I get this time is:

"Syntax error in FROM clause"

When I click debug, it highlights the line of code

rs.Open "Reject Count", cn, adOpenKeyset, dLockOptimistic, adCmdTable

Reject count is the table within access that is being appended, and the rest is what was included in the code I was coping over. One thing that I noticed is that this machine I am on has .NET installed so all the references you had me check where version 10 (if that makes any sense) I believe some of the problems I have is the difference in VB 6 and .NET? Anyhow, we had a programmer here full time but recently laid them off. I have been "asked" to try and tae over the things that person did. Being a network person, I have very little knowledge of VB, and really appreciate all this help. I think we are close, but not quite there. Please help if you still have time and patience with me If I knew you in real life, I would take you all out for a drink or two on me, lol. Thanks again.

 

[hysteria]

try putting brackets around the name of the table,I think the problem is because u have a space between the table name

rs.Open "[Reject Count]", cn, adOpenKeyset, dLockOptimistic, adCmdTable

all these lil things can be a pain...

thanx for the drinks hehe...
seeya

 

[Mortalous]

LOl, you are right about all these little steps. one problem leads to the next, but thankfully I have your great self to help me so far! Anyways, my next thing is this, and though I don't know much i think I have it figured out, though I don't know how exactly to finish it.

I run it with the brackets and now it comes up with an error stating:

The MS jet database engine cannot find the input table or query 'Reject Count'. make sure that it exists and that the name is spelled correctly.

Well here is the code:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\Steve\Desktop\excelToaccess\db1.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "[Reject Count]", cn, adOpenKeyset, adLockOptimistic, adCmdTable

I am noticing that access isn't opening "db1.mdb". Is this a problem? I would think it would open access up first then execute the code to open the recject Count table? What do you think? thanks again...

 

[Mortalous]

I also have renamed the reject Count table to make sure it does not have the space and I have adjusted the code accordingly.

 

[Mortalous]

I think it was my fault. I had an extra in the name of the table, doh! Owell, I will continue to try and see what I can do..thanks. .

 

[Mortalous]

Finally, I have it completed. Thank you so very much for this help. You will never know jsut how much of a difference you have made to my life with this help. You are an inspiration to me and everyone!

 

[hysteria]

so it works...
Thats great...heh...
ive just started working with access a couple of months back..theres too much to do..however im still working with access 97 so i dunno how much of a difference that makes in other versions of it...
anyways...happy to know..that i could be of help..