Excel - Leer datos de access desde excel usando ADODB

   
Vista:

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 10/03/2016 16:24:51
Hola a todos, denme una mano por favor.
Pretendo desde una hoja excel, leer datos de una base de datos access. Usando DAO la consulta funciona y trae datos sin problemas, pero como debo leer datos de otras fuentes de datos, me "obliga" a usar ADO y alli esta mi problema

Este código uso para DAO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
' Paso 1:Asignación de bd, recordset y querydef
Dim MyDatabase As DAO.database
Dim MyQueryDef As DAO.Querydef
Dim MyRecordset As DAO.Recordset
 
' Paso 2: Identificar la base de datos y la consulta.
Set MyDatabase = DBEngine.OpenDatabase ("C:\Mis Documentos\Genera datos.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("Filtra UltDatos")      'esta query esta en la BD
 
'Paso 3: Abrir la consulta. Los resultados de la consulta son archivadas dentro de MyRecordset.
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Paso 4: Se copian los datos a Excel, para este caso a la celda "I4"
ActiveSheet.Range("I4").CopyFromRecordset MyRecordset


Esto funciona muy bien pero no se como hacerla funcionar con ADODB, primero porque no hay similitud con querydef y ademas me aparece un error: No se puede leer ningún registro; no tiene permiso para READ en xxx


Lo que he hecho en ADODB es lo siguiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Dim cs As String
Dim sPath As String
Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
sPath = ThisWorkbook.Path & "\Genera datos.mdb"
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"
 
Set cn = New ADODB.Connection
cn.Open cs
 
Set rs = New ADODB.Recordset
With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
End With
 
sql = "SELECT [xxx].campo1, [yyy].campo2, Sum([campo3]+[campo4]) AS Total " & _
        "FROM [xxx] LEFT JOIN [yyy] ON [xxx].campo1 = [yyy].campo5 GROUP BY [xxx].campo1"
 
rs.Open sql, cn
 
ActiveSheet.Range("I4").CopyFromRecordset rs
 
rs.Close
cn.Close
 
Set rs = Nothing
Set cn = Nothing


Por favor si alguien me puede decir cual es el problema de tal error. parece un tema de seguridad o acceso aún cuando dicha base de datos no se ha tocado o modificado
La consulta he tenido como ponerla como instrucción sql por que en si esta como una query dentro de la base de datos original y se llama "Filtra UltDatos."
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Leer datos de access desde excel usando ADODB

Publicado por JHON JAIRO (23 intervenciones) el 10/03/2016 20:16:28
Hola en el Script de visual basica primeramente debe tener referenciado la libreria Microsoft ActiveX Data Objets 6.1 Library o la version más actualizada activa.

Para ello se ubica en el menu Herramientas, luego referencias y por ultimo selecciona la libreria correspondiente
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 11/03/2016 21:18:23
Hola Jhon Jairo, gracias por responder.
En efecto si tengo habilitada esa referencia: Microsoft ActiveX Data Objets 2.0 Library
Pero aún con ello me da el error: No se puede leer ningún registro; no tiene permiso para READ en xxx

El código esta bien ??
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 11/03/2016 22:03:44
Creoq ue la 2.0 no permite eso ... si no a partir de la 2.5

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 12/03/2016 00:59:57
No funciona lo que me sugieres Andres Leonardo !!!
Tengo office 2010 y mi access es 2003, deseo poder conectarme con ADODB

He cambiado mi codigo por lo que he podido encontrar en la red pero nada que funcione.
la linea provider la he cambiado de 14 a 13 a 12 a 11 y tampoco funciona y me da el error que adjunto

provider = "Microsoft.ACE.OLEDB.11.0"




referencias
error


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
' estas dos lineas a inicio del modulo
Public MiConexion As New ADODB.Connection
Public Rs As New ADODB.Recordset
 
 
Sub Conecta()
Set MiConexion = New ADODB.Connection
    With MiConexion
        .provider = "Microsoft.ACE.OLEDB.11.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Genera datos.mdb"
        .Open
    End With
End Sub
 
 
Sub TraeDatosAccess()
Dim MiQuery As String
Call Conecta
Set Rs = New ADODB.Recordset
 
MiQuery = "SELECT [Filtra UltFecha].campo1, [Historico].campo2, Sum([DfPtCtr]+[DfAgCtr]) AS DifTotal " & _
          "FROM [Filtra UltFecha] LEFT JOIN [Historico] ON [Filtra UltFecha].campo1 = [Historico].campo1 " & _
          "GROUP BY [Filtra UltFecha].campo1, [Historicol].campo2"
 
 
Rs.Open MiQuery, MiConexion, adOpenKeyset, adLockOptimistic, adCmdText
 
' La siguiente linea Copiaba el contenido del recordset en su totalidad en la fila y columna indicada
 Worksheets(3).Cells(4, 14).CopyFromRecordset Rs
 
    Rs.Close
    MiConexion.Close
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 14/03/2016 18:12:22
me funciona perfecto tu codigo en mi maquina (aun sin marcar el access 15 ) por qu eno defines objetos de access....
Parece que el driver (o conectar a la base) no esta correctamente instalado

pregunto tu maquina es de 64 bits??

Con todo instala esto

https://www.microsoft.com/en-us/download/details.aspx?id=13255

**Pongo el link con permiso de los admoinistradores creo que no es incorrecto
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 16:49:59
Hola Andres !!!

Por favor reenviame el código o las referencias que tienes instladas... quiero ver que pasa que aca no me trabaja
Mi maquina es de 32 bits y tengo excel 2010 y access 2003

Por favor ayudame con este tema
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 17:19:54
Adjunto

Dentro de la carpeta esta la imagen requerida.

Y el ejemplo como a mi si me funciona.... solo debes colocar en el c:\excel

los dos archivos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 17:42:39
Gracias Andres !!

Revisaré lo enviado para adaptar a mi aplicación. Me quedó preguntarte por ese enlace y/o actualización access database engine.
Hay que instalarlo? hay que desinstalar la versión 2003 de acces que utilizo?

Ojala funcione lo que me envias. Me es urgente solucionar esto.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 17:47:47
Ese segun lo que lei... Es el adapter q en tu caso parece que no esta instalado correctamente tomalo.como.ultimo.recurso....
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 18:23:03
Andres !!!

Tu ejemplo me trabaja sin problemas, lo baje una carpeta cree un boton y ejecute la macro y funciona muy bien

Pero cuando cambio mi base de datos y consulta sql me sale error
Si te fijas solo solo cambie el nombre de la BD y puse como comentario la tuya, y luego en la consulta sql lo mismo

He cambiado las referencias tal cual esta como las tuyas y error que sale es:


referencias
error


Sub MacAndres()
'
' MacAndres Macro
'

Dim cs As String
Dim sPath As String
Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'sPath = ThisWorkbook.Path & "\holcim.mdb"
sPath = ThisWorkbook.Path & "\Genera CSV.mdb"
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

Set cn = New ADODB.Connection
cn.Open cs

Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

'sql = "select * from CALENDARIOFESTIVOS"

sql = "SELECT Max(Historico.Fecha) AS FDiferida FROM Historico"


rs.Open sql, cn

ActiveSheet.Range("N3").CopyFromRecordset rs

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing
Application.Goto Reference:="MacAndres"

'ActiveWorkbook.Save
End Sub
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 18:34:39
Si no t es problema puedes enviar la base o una parte de esta???
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 18:38:40
Mira si la base pesa mas de 2GB ... podrias compactarla?=?? quizas le falte eso....

otra cosa que podrias hacer es tratar de migrarla a otra MDB nuevo veo que ese error te da cuando no puedes acceder a la base corrrectametne

en todo caso si puedes envia la base o parte de la base si no tienes problemas para intentar hacerlo....

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 20:26:22
Gracias por tu tiempo e interes.

Te adjunto la BD compactada y datos para que no cupe espacio. También adjunto mi planilla excel y es en la hoja "UlttPotencial" donde deseo traer los datos de la BD de access, lo he probado con el botón que allí puse.
En el código VBA podras encontrar como trabajaba antes con DAO el cual ya saque la refenrecia para trabajar todo en ADO. Utilizo ADO porque leo datos de un scada sin ningún problema.


Voy intentando migrar el resto de objetos de la BD para seguir haciendo pruebas y lo que recomiedas.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 20:44:47
Miguel no veo que hayas adjuntado nada.... Saludos

**Si no puedes por aca podrias subirla a algun sitio de donde sacarla...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 21:20:37
QUe raro, estoy otra vez reenviando con el archivo zip.
Intente crear una nueva BD e importe las tablas pero sigue dando el mismo error.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 21:45:00
Tiene que ver el que use Archivo de Información de Grupo de Trabajo (*.mdw)

No creo sea el problema porque tu ejemplo y base de datos que me enviaste la guarde en un carpeta y la abri y ejecuté sin problemas.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 22:17:47
Bueno la verdad no lo se que era ... al parecer es eso .. pero en todo caso ya lo resolvi segun como te dije

Espero que con esto te funcione tu codigo Saludos

**Migre todo a una base nueva !! y listo fue como te dije.....
**Si algun dato estaba corructo en la base dañada lo omitio y copio lo nievo

Espero que con esto ya este solucionado
Resuelto
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 15/03/2016 23:15:27
Excelente Andres !!

Yo también cree una BD y no me dio resultado, quizás este amarrado con el archivo de grupo de trabajo, es muy raro.

Para poder enviarte la BD de prueba tuve que "importar" la tabla "Historico" porque es muy pesada y esta físicamente en otra BD, yo para fines de lo que estoy haciendo cree un vinculo con la BD principal donde reside.

Tu ejemplo funciona y muy bien pero si yo vinculo con mi tabla original (mismo nombre), otra vez da el problema, si cambio y hago con la tabla local de la BD ejemplo funciona muy bien.

Al parecer esto es un tema de seguridad de las tablas o la misma BD.

Un nuevo aprendizaje por tu aporte e interés.

Una consulta mas: Es posible que un recordset sea origen de una nueva consulta con otra tabla fisica o vinculada de una BD de datos. ?? has hecho algo asi?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 15/03/2016 23:21:56
Hola que bueno que te haya servido .. ahora te cuento como lo hice para que lo hagas tu

Crea una nueva base en access en version mdb como la que tienes ... pero ebn BLANCO asi podras importat toda tu base....

Anda a datos externos .. access
Importar tablas .. conusltas

No la opcion de vinvular si no la deimportar.... .. seleccionas tu base maestra....y de ahi importa todoo tablas consultas todo....

luego de eos... revisa


Una consulta mas: Es posible que un recordset sea origen de una nueva consulta con otra tabla fisica o vinculada de una BD de datos. ?? has hecho algo asi?

Lo que se me ocurre es recorrer el recordset .... para generar una nueva. consulta... pero en tu caso como es una fecha seguen el ejemplo yo dejaria el valor de record set ... en alguna variable para que esta sirva... una vez que sales de ese sub... crear otro llamando otra consulta nueva .... espero que me hayas entendido

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 16/03/2016 00:35:59
Lo que me recomiendas para crear una nueva BD lo hice tal cual lo comentas pero no me dio resultado. El archivo "historico" esta fisicamente en otra base de datos porque tiene muchos datos históricos; yo "importe" las tablas pequeñas y las consultas y creeme que tampoco funcionó con las tablas fijas; menos me funcionó luego que vincule con la tabla "histórico".

Lo raro es que en tu ejemplo si funciona pero es allí donde deseo "agregar" mi tabla "histórico" vinculada pero lo hago y me da el mismo error.
Parece que hay algo con la creación de la BD y la seguridad.

Cada vez que abro una BD siempre pide el usuario y clave, trabajo con archivo de grupo de usuarios (.mdw) la clave no tiene solo se coloca el usuario que ya viene por defecto porque lo lee de la red.

Para descartar el tema de seguridad si yo quisiera que en el enlace o conexion a esa BD si quisiera colocarle los parametros de usuario y clave, como lo haria o donde lo pondria?


Respecto a mi otra consulta, me parece muy logico lo que dices, como solo es un dato el que me trae lo guardare en una variable y en la nueva consulta hago referencia a esa variable. PAra este problema que tengo me serviria hacer eso. Lo que no se es como se haria si el primer recordset trae varias registros y campos. Por el momento me sirve lo primero.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Andres Leonardo

Leer datos de access desde excel usando ADODB

Publicado por Andres Leonardo (483 intervenciones) el 16/03/2016 14:58:03
Sabes nunca habia trabajado con lo que dices de seguridad.... vaya que me parece complicado eso... en todo caso .. toda tu base la migre a la mia y funciono como ves.....por lo que no sabria donde le pones el usuario y clave habria que investigarlo....

Respecto a tu otra consulta Para eso toca recorrer el recordset asi
Con esto podrias revisar todos los datos que te devuelve el recorset.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Rs.Open SQL, conexion
    If Rs.EOF = True And Rs.BOF = True Then
    MsgBox "No existen Resultados para mostrar", vbInformation
    Rs.Close
    Exit Sub
    End If
 
j = Rs.Fields.Count  - 1
    While Not Rs.EOF
        i = i + 1
        k = 0
        While k < j
                    variable = Rs(k)
            k = k + 1
        Wend
        Rs.MoveNext 'avanza al siguiente registro del rs.
    Wend
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 16/03/2016 15:22:33
Excelente ejemplo Andres!!

Una vez mas te agradezco todo tu tiempo, dedicación e interés en ayudarme

Seguiré probando como poder acceder a ese archivo vinculado.

Gracias Mil !!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Leer datos de access desde excel usando ADODB

Publicado por Miguel (38 intervenciones) el 16/03/2016 20:06:34
Hola Andres !!!

Logré que me permita acceder a la tabla "historico" que estaba vinculada.

Como te expliqué antes, dicha tabla esta en una BD mas grande y tiene mucha historia pero al intentar vincular con dicha tabla me daba el mismo error de acceso. De tanta prueba y búsqueda por internet, probé esto que adjunto y me dio resultado.

Para fines de leer datos de mi scada o de la BD access con grupo de trabajo utilizo conexiones diferentes. No se o no tengo el formato o sintaxis completa para la conexión con "Microsoft.ACE.OLEDB.12.0" y por eso he tenido que usar: "Microsoft.Jet.OLEDB.4.0"
Quizás tenga su correspondiente forma o parámetros pero no los tengo como para probar.

De todas formas adjunto por si le pueda servir a alguien.


Para Leer datos de BD access con grupo de trabajo:

cs = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & "Data Source=" & sPath & ";" & _
"User ID=miuser;" & "Password=miclave;" & "Jet OLEDB:System Database=C:\Usuarios.mdw"



Para Leer datos del scada:

cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & BaseDatos & "; " & _
"Data Source=" & Servidor & "; " & _
"User id=" & Usuario & ";" & _
"Password=" & Clave
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar