Send data from excel to access

[jaffar2000]

Hi all,
Appreciate your help with this one:

I have users filling data (in various cells) in excel template file.
Each time a user is filling the same cells and saves the file.

Is it possible with a macro/vba, that whenever the user saves the file, the data will be exported to a specific table I have in access file?

Thanks.
Jan.

 

[sparks80]

Hi,

I would use ADODB to connect to your database from Excel. I have written some example code here for you, but it is untested.

You can use the "Workbook_BeforeSave" event to run the code each time the user saves the file. You will need to modify the code to copy the relevant records.

If you need further help can you possibly post the details of the excel worksheet, and the table/field details in the database.

Sub CopyDataFromExcelToAccess()

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set cn = New ADODB.Connection

  'set up connection string
  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
         "Data Source=c:\myfolder\mydatabase.mdb;"   
  'connect to database
  cn.Open

  Set rs = New ADODB.Recordset
  
  rs.Open "YourTableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  

  With rs
    .AddNew
    .Fields(YourFieldName) = Sheets("SheetName").Range("A1")
    .Update
  End With

  ' Close the recordset and connection
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
End Sub

 

[jaffar2000]

thank for your help.
I copied and modified the code to fit the specific cells & DB fields.
When running the code I get a compile error saying:
"user-defined type not defined" and the code statement "Dim cn As ADODB.Connection" is highlighted.

by the way, I'm using access2007 and excel 2010.

Any ideas?

 

[sparks80]

Hi,

Sorry I forgot to mention one vital piece of information.

In order to use ADO from Excel you need to reference the activex data objects library file.

To do this:
open the VBA Editor (ALT+F11)
go to the Tools menu and select References
Scroll down the list until you reach "Microsoft ActiveX Data Objects x.x Library" and check the tick box.

Hopefully it should now work. There may be multiple versions installed, so I suggest you use the latest version.

 

[jaffar2000]

ok we solved the last issue.
Now getting run time error with message "could not find file...".
I check the location and name and they are correct but still got this message.

 

[sparks80]

Hi,

Just a quick thought - does the file path include spaces. In this case I think you would need to enclose the path with quotation marks. Try this:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
         "Data Source=" & chr(34) & "c:\myfolder\mydatabase.mdb" & chr(34) & ";"

 

[jaffar2000]

Don't have spaces, still having the same error.
Do I have to install any other drivers?

 

[sparks80]

Hi,

I use an older version of access and I think the database provider you need for Access 2007 is different, so the connection string should be:

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myfile.accdb;Persist Security Info=False;"

Have a look at this page for more information:

http://www.connectionstrings.com/access-2007

 

[jaffar2000]

Thanks.
I believe the connection is working now, however..new issue:
"run time error 3265 item could not be found in the collection corresponding to the requested name or ordinal".

Here is the location of the error:

Sub CopyDataFromExcelToAccess()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

'set up connection string
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyFolder\Requests.accdb;Persist Security Info=False;"

'connect to database
cn.Open

Set rs = New ADODB.Recordset

rs.Open "Requests", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew
.Fields(rdate) = Sheets("form").Range("H4")
.Fields(requested_by) = Sheets("form").Range("B4")
.Fields(Description) = Sheets("form").Range("B11")
.Fields(department) = Sheets("form").Range("D4")

.Update
End With

' Close the recordset and connection
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[sparks80]

Hi,

I think it is expecting the field name as a string, so try:

.Fields("rdate") = ...

 

[jaffar2000]

Working as expected!!!
you made my day, can't thank you enough.
J

 

[amisha]

Hi ,



I'm trying to use the code above and I'm getting "run-time error - automation error" in line

"rs.Open "input", cn, adOpenKeyset, adLockOptimistic, adCmdTable"

I uploaded a screenshot of VB editor

thanks