
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