Connecting to accdb with Excel 2003
 

Hi,
I have a database that was developed using access 2007 and is split into
front ends and one backend. One of the things that I am doing with the data
in the backend is making a pivot table in Excel 200x. I have done this
without any problems in Excel 2007 but I am having trouble doing it in Excel
2003. Can you please tell me how to connect to an accdb file from excel 2003
so that I can generate the pivot table I need?
Thank you,
Preston

Connecting to accdb with Excel 2003
 

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String

'MAIN PROC
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "path and file name", "admin", ""
cn.CursorLocation = adUseClient

strsql = "SELECT frOM statement'
rs.Open strsql, cn

ECT...


Regards,

Devin

"Preston" wrote:

Hi,
I have a database that was developed using access 2007 and is split into
front ends and one backend. One of the things that I am doing with the data
in the backend is making a pivot table in Excel 200x. I have done this
without any problems in Excel 2007 but I am having trouble doing it in Excel
2003. Can you please tell me how to connect to an accdb file from excel 2003
so that I can generate the pivot table I need?
Thank you,
Preston

Connecting to accdb with Excel 2003
 

Also make sure that you have the Microsoft activex dataobject 2.6 reference
selected in your excel file.



"Preston" wrote:

Hi,
I have a database that was developed using access 2007 and is split into
front ends and one backend. One of the things that I am doing with the data
in the backend is making a pivot table in Excel 200x. I have done this
without any problems in Excel 2007 but I am having trouble doing it in Excel
2003. Can you please tell me how to connect to an accdb file from excel 2003
so that I can generate the pivot table I need?
Thank you,
Preston

Connecting to accdb with Excel 2003
 

Try using ACE instead of Jet
Provider=Microsoft.ACE.OLEDB.12.0;Data
Standard security

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;


With database password
This is the connection string to use when you have an Access 2007 database
protected with a password using the "Set Database Password" function in
Access.
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database
Password=MyDbPassword;


DataDirectory functionality

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=|DataDirectory|\myAccess2007file.accdb;Pers ist Security Info=False;


"Preston" wrote:

Hi,
I have a database that was developed using access 2007 and is split into
front ends and one backend. One of the things that I am doing with the data
in the backend is making a pivot table in Excel 200x. I have done this
without any problems in Excel 2007 but I am having trouble doing it in Excel
2003. Can you please tell me how to connect to an accdb file from excel 2003
so that I can generate the pivot table I need?
Thank you,
Preston

Connecting to accdb with Excel 2003
 

Mike, it worked. Jet didn't work though. It takes the ACE provider to connect
to accdb. When i tried Jet it told me "database format not recognized." I
guess that means that machines which are running Office 2003 require the 2007
Office System Driver: Data Connectivity Components software to connect? I
will have to install this on the machine in order to get it to connect with
Excel 2003. Thank you again for your help. :)
Preston

"Mike" wrote:

Try using ACE instead of Jet
Provider=Microsoft.ACE.OLEDB.12.0;Data
Standard security

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;


With database password
This is the connection string to use when you have an Access 2007 database
protected with a password using the "Set Database Password" function in
Access.
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database
Password=MyDbPassword;


DataDirectory functionality

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=|DataDirectory|\myAccess2007file.accdb;Pers ist Security Info=False;


"Preston" wrote:

Hi,
I have a database that was developed using access 2007 and is split into
front ends and one backend. One of the things that I am doing with the data
in the backend is making a pivot table in Excel 200x. I have done this
without any problems in Excel 2007 but I am having trouble doing it in Excel
2003. Can you please tell me how to connect to an accdb file from excel 2003
so that I can generate the pivot table I need?
Thank you,
Preston