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

data import and export in to access
 

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" wrote in message ups.com...
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

data import and export in to access
 

Hi Ron,

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

please help me out.

cheers,
nuti

data import and export in to access
 

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" wrote in message oups.com...
Hi Ron,

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

please help me out.

cheers,
nuti

data import and export in to access
 

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?

data import and export in to access
 

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" wrote in message ups.com...
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?

data import and export in to access
 

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

data import and export in to access
 

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

data import and export in to access
 

Try to test it this evening

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


"nuti" wrote in message ups.com...
Ron,
read the range of the last three fields in sequence....shuold b
F,G,H..........

data import and export in to access
 

Hi Ron,

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

data import and export in to access
 

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" wrote in message ups.com...
Hi Ron,

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