data import and export in to access

Hello group,

I want to know the Code which exports data to an Access table
from an Excel worksheet and the code which imports data
from an Access table to a worksheet.

i want to run it as a macro .

Please help me with this problem.

cheers
Nuti
[email protected]

Hi

I am working on a webpage about this and hope to finish it next week

You can look here for information
http://www.erlandsendata.no/english/...php?t=envbadac




--
Regards Ron de Bruin
http://www.rondebruin.nl


"nuti" <[email protected]> wrote in message news:[email protected]...
> Hello group,
>
> I want to know the Code which exports data to an Access table
> from an Excel worksheet and the code which imports data
> from an Access table to a worksheet.
>
> i want to run it as a macro .
>
> Please help me with this problem.
>
> cheers
> Nuti
> [email protected]
>

Hi Ron,

I have visited the link.
But it is throwing error.-----runtime error 3265

please help me out.

cheers,
nuti

Have you set the reference in the VBA editor and are the File, Table and field names correct in the code

--
Regards Ron de Bruin
http://www.rondebruin.nl


"nuti" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
>
> I have visited the link.
> But it is throwing error.-----runtime error 3265
>
> please help me out.
>
> cheers,
> nuti
>

what shuold be the reference?

correct me if iam wrong---
---does the code take the excel sheet which is open/from the excel from
which the macro is run?

> what shuold be the reference?

From the website :

The macro examples assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object
Library.
Use ADO if you can choose between ADO and DAO for data import or export.


> correct me if iam wrong---
> ---does the code take the excel sheet which is open/from the excel from
> which the macro is run?

It use the active sheet but you can include the sheet name if you want

Range("C1")

' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"nuti" <[email protected]> wrote in message news:[email protected]...
> what shuold be the reference?
>
> correct me if iam wrong---
> ---does the code take the excel sheet which is open/from the excel from
> which the macro is run?
>

Hi Ron,

Actually i hav added the reference in the very begining.

the excel sheet has 7 columns....ServerName,PrinterName.......

when im running the debugger it is pointing to ---.Fields("ServerName")
= Range("A" & r).Value

i had created a database by the name testaccess and tablename as
table1.




*******************************
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/4/2006 by Infosys
'

'

' 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=E:\testaccess\testaccess.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2
' 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("ServerName") = Range("A" & r).Value
.Fields("PrinterName") = Range("B" & r).Value
.Fields("ShareName") = Range("C" & r).Value
.Fields("PortName") = Range("D" & r).Value
.Fields("DriverName") = Range("E" & r).Value
.Fields("Comment") = Range("E" & r).Value
.Fields("Location") = Range("E" & r).Value
.Fields("Sepfile") = Range("E" & 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

Ron,
read the range of the last three fields in sequence....shuold b
F,G,H..........

Try to test it this evening

--
Regards Ron de Bruin
http://www.rondebruin.nl


"nuti" <[email protected]> wrote in message news:[email protected]...
> Ron,
> read the range of the last three fields in sequence....shuold b
> F,G,H..........
>

Hi Ron,

I have tried to run this macro (from the active excel sheet).
but it is throwing the same error.

hi Nuti

Send me a private e-mail and i send you a very basic example


--
Regards Ron de Bruin
http://www.rondebruin.nl


"nuti" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
>
> I have tried to run this macro (from the active excel sheet).
> but it is throwing the same error.
>