Controlling ADP from Excel with VBA 
Author Message
 Controlling ADP from Excel with VBA

I have a website which extracts data from SQL Server which is the back-end
of an Access Data Project.

Very simply, I have a procedure which needs to run every night to extract
data from Excel so I want to write some VBA. I already have a macro written
in the Access Data Project and I want Excel to run this macro to extract the
data from Excel and put it on the SQL Server. I see the following steps:

1) Create and instance of Access

2) Open the Access Data Project to connect to the SQL Server

3) Run the Macro

4) Close Access

I have the following macro

Sub ExpMac()
    Dim objAccess As Access.Application
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "c:\msoffice\mydb.adp", False,
"MyPassword"
    objAccess.Visible = True
    objAccess.DoCmd.RunMacro "GetData"
    objAccess.Quit
    Set objAccess = Nothing
End Sub

Unfortunately, it opens the project mydb.adp without connecting to the
back-end SQL Server. The Macro then fails to run because the tables aren't
there. I'm not sure if the tables don't connect because the password is
incorrect or whether there is a problem with the OpenCurrentDatabase command
for opening Access Data Projects.

 Does anyone know how to open and ADP project and have it connect to the
data? I'm keen to do it this way as I'm reasonably familiar with Excel VBA
and Access VBA.

Alternatively, does anyone have a cute bit of VBA which will connect to an
SQL Server and do something equivalent to "transferspreadsheet"?

thanks

Andrew



Tue, 23 Mar 2004 03:53:01 GMT
 Controlling ADP from Excel with VBA

Andrew,

Have you thought about using SQL Server's DTS function to import the Excel
data directly into SS7, bypassing Access ADP altogether?



Quote:
> I have a website which extracts data from SQL Server which is the back-end
> of an Access Data Project.

> Very simply, I have a procedure which needs to run every night to extract
> data from Excel so I want to write some VBA. I already have a macro
written
> in the Access Data Project and I want Excel to run this macro to extract
the
> data from Excel and put it on the SQL Server. I see the following steps:

> 1) Create and instance of Access

> 2) Open the Access Data Project to connect to the SQL Server

> 3) Run the Macro

> 4) Close Access

> I have the following macro

> Sub ExpMac()
>     Dim objAccess As Access.Application
>     Set objAccess = CreateObject("Access.Application")
>     objAccess.OpenCurrentDatabase "c:\msoffice\mydb.adp", False,
> "MyPassword"
>     objAccess.Visible = True
>     objAccess.DoCmd.RunMacro "GetData"
>     objAccess.Quit
>     Set objAccess = Nothing
> End Sub

> Unfortunately, it opens the project mydb.adp without connecting to the
> back-end SQL Server. The Macro then fails to run because the tables aren't
> there. I'm not sure if the tables don't connect because the password is
> incorrect or whether there is a problem with the OpenCurrentDatabase
command
> for opening Access Data Projects.

>  Does anyone know how to open and ADP project and have it connect to the
> data? I'm keen to do it this way as I'm reasonably familiar with Excel VBA
> and Access VBA.

> Alternatively, does anyone have a cute bit of VBA which will connect to an
> SQL Server and do something equivalent to "transferspreadsheet"?

> thanks

> Andrew



Wed, 24 Mar 2004 02:18:33 GMT
 Controlling ADP from Excel with VBA
Kevin,

thanks for the suggestion. I did look at this, but it looks as though it
needs Enterprise Manager and you don't get that with Office XP

I found a useful some code at Rick Dobson's site

http://www.programmingmsaccess.com/Samples/VBAProcs/VBAProcToOpenAnAc...
ject.htm

which does this and I'm trying to figure out how to get it working ... I
seem to have an ODBC error:

Prb: -2147467259 (80004005) [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified

I think I have it configured wrongly in the security section:

appAccess.CurrentProject.OpenConnection _
"PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;" & _
"PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & _
dbname & ";DATA SOURCE=" & srvname

Thanks again for your suggestion.

Regards

Andrew


Quote:
> Andrew,

> Have you thought about using SQL Server's DTS function to import the Excel
> data directly into SS7, bypassing Access ADP altogether?




> > I have a website which extracts data from SQL Server which is the
back-end
> > of an Access Data Project.

> > Very simply, I have a procedure which needs to run every night to
extract
> > data from Excel so I want to write some VBA. I already have a macro
> written
> > in the Access Data Project and I want Excel to run this macro to extract
> the
> > data from Excel and put it on the SQL Server. I see the following steps:

> > 1) Create and instance of Access

> > 2) Open the Access Data Project to connect to the SQL Server

> > 3) Run the Macro

> > 4) Close Access

> > I have the following macro

> > Sub ExpMac()
> >     Dim objAccess As Access.Application
> >     Set objAccess = CreateObject("Access.Application")
> >     objAccess.OpenCurrentDatabase "c:\msoffice\mydb.adp", False,
> > "MyPassword"
> >     objAccess.Visible = True
> >     objAccess.DoCmd.RunMacro "GetData"
> >     objAccess.Quit
> >     Set objAccess = Nothing
> > End Sub

> > Unfortunately, it opens the project mydb.adp without connecting to the
> > back-end SQL Server. The Macro then fails to run because the tables
aren't
> > there. I'm not sure if the tables don't connect because the password is
> > incorrect or whether there is a problem with the OpenCurrentDatabase
> command
> > for opening Access Data Projects.

> >  Does anyone know how to open and ADP project and have it connect to the
> > data? I'm keen to do it this way as I'm reasonably familiar with Excel
VBA
> > and Access VBA.

> > Alternatively, does anyone have a cute bit of VBA which will connect to
an
> > SQL Server and do something equivalent to "transferspreadsheet"?

> > thanks

> > Andrew



Wed, 24 Mar 2004 19:23:30 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Secure ADP, Distribute ADP and copy ADP

2. viewing calculated members in Excel PivotT with Excel 2000 and Excel XP

3. How do I view all VBA code for an ADP

4. Linking to a SQL Server table through VBA in an ADP

5. Add a New User to a SQL-Server 2000 via vba and adp

6. Excel VBA ODBC Stored Procedure calls

7. (A2000 adp project reference another A2000 adp project)

8. MDX vs. Excel PivotTable API VBA

9. Mixing Excel, VBA and SQL server

10. Connecting to an SQL ODBC source from Excel VBA

11. Member Filter using ADOMD in VBA for Excel XP Pivot-Table

12. VBA to export from Excel to SQL Server


 
Powered by phpBB® Forum Software