ADO connection string to Access 2007 not working

B

Bobby Burns

New member

Local time

Today, 11:22

Joined

May 2, 2007

Messages

4

• May 25, 2007

• #1

Hi there,

I am trying to connect to an Access 2007 database (.accdb ext) from an Excel 2007 workbook (.xlsm ext) using ADO and the following connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabaseName.accdb;"

When I try and connect, I get the error message "Unrecognised database format 'MyDatabaseName.accdb'".

Can anyone advise me on where I am going wrong.

Help much appreciated.

 

boblarson

Smeghead

Local time

Today, 03:22

Joined

Jan 12, 2001

Messages

32,059

• May 25, 2007

• #2

What OS are you using (do you have all of your updates)? And, are you able to set a reference to Microsoft ActiveX Data Objects 2.8?

 

B

Bobby Burns

New member

Local time

Today, 11:22

Joined

May 2, 2007

Messages

4

• May 25, 2007

• #3

I'm using Windows XP Professional and yes, I have a reference set up ok to ActiveX 2.8.

 

B

Bobby Burns

New member

Local time

Today, 11:22

Joined

May 2, 2007

Messages

4

• May 25, 2007

• #4

What exactly do you mean by 'do I have all of my updates?

 

boblarson

Smeghead

Local time

Today, 03:22

Joined

Jan 12, 2001

Messages

32,059

• May 25, 2007

• #5

The Windows Updates and Office Updates from the Microsoft site. That would entail going to http://update.microsoft.com/microso...update.microsoft.com/microsoftupdate&ln=en-us

 

R

RoyVidar

Registered User.

Local time

Today, 12:22

Joined

Sep 25, 2000

Messages

805

• May 25, 2007

• #6

New engine (ACE), try

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabaseName.accdb;"

 

B

Bobby Burns

New member

Local time

Today, 11:22

Joined

May 2, 2007

Messages

4

• May 29, 2007

• #7

Thanks Roy, that works fine now. Thanks Bob too for your replies.

 

H

helenachurchills

New member

Local time

Today, 04:22

Joined

Aug 12, 2009

Messages

1

• Aug 12, 2009

• #8

I have the new connect string but am not able to find how to actually connect and populate a recordset. I used to set db as new adodb.recordset. That doesn't work. I read you have to do something different but cannot find it.

 

J

Jayendran Chellam

New member

Local time

Today, 15:52

Joined

Dec 3, 2010

Messages

1

• Dec 3, 2010

• #9

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\MyFolder\myDB.accdb;Persist Security Info=False"

 

D

daimadoshi

New member

Local time

Today, 12:22

Joined

Sep 22, 2011

Messages

1

• Sep 22, 2011

• #10

Hello,

Excuse me to up this topic, but I have some probleme using connection to access 2007.
I can connect to the database ACCESS 2007 from EXCEL 2007 but when i try to use a recordset, I have an error message on "open" instruction.

Code :
Sub Nouvel_utilisateur_clic()

Dim chemin As String

chemin = "T:\Informatique\Projets\Gestion du Parc\GesParc.accdb"

Set cnx = New ADODB.Connection

' Connexion à la base
'ConnectDB cnx, chemin

'Définition du recordset

Dim rec As ADODB.Recordset
Dim Rs0 As DAO.Recordset

'Définition du pilote de connexion
'cnx.Provider = "Microsoft.ACE.OLEDB.12.0"
'Définition de la chaîne de connexion
cnx.ConnectionString = chemin
'Ouverture de la base de données
cnx.Open

Set rec = New ADODB.Recordset
rec.ActiveConnection = cnx

'Ouverture de la table
rec.Open "Select Prenom from User", cnx --> I have the error message "Syntax error in FROM clause" (Maybe not exactly this message because i have a french version)

Can you help me please ?

Thank you

Daimadoshi

 

Last edited: Sep 22, 2011