Help - VB Script call MS Access 
Author Message
 Help - VB Script call MS Access

Dear All,

I tried the following scripts to call table and query in MS Access and
they worked.

Set rs1=OBJdbConn.Execute("select * from tableA")
Set rs1=OBJdbConn.Execute("execute Q1")
  where Q1 is the Query stored in MS Access

    However, when I applied them to call the Macro or Module in Ms
Access, I got error. Would you also advise how to call the Macro in
Excel. Thanks a lot.

Best Regards,
Stanley Ngai



Tue, 05 Oct 2004 17:40:40 GMT  
 Help - VB Script call MS Access
You are using an ADO through an OLEDB provider for Access?

I believe that to call objects such as macros you may have to look at using
DAO, not ADO as a means of accessing the database.

I'm not sure about accessing module functions through DAO.

Chris Barber.


Dear All,

I tried the following scripts to call table and query in MS Access and
they worked.

Set rs1=OBJdbConn.Execute("select * from tableA")
Set rs1=OBJdbConn.Execute("execute Q1")
  where Q1 is the Query stored in MS Access

    However, when I applied them to call the Macro or Module in Ms
Access, I got error. Would you also advise how to call the Macro in
Excel. Thanks a lot.

Best Regards,
Stanley Ngai



Tue, 05 Oct 2004 18:20:12 GMT  
 Help - VB Script call MS Access
Hmm.
I had a good long search on the MSDN library documentation and came to the
conclusion that for Macros and Module functions, you will have to use the
COM Object Model.

eg.

    Set pobjAccess = CreateObject("Access.Application")

With pobjAccess


You are using an ADO through an OLEDB provider for Access?

I believe that to call objects such as macros you may have to look at using
DAO, not ADO as a means of accessing the database.

I'm not sure about accessing module functions through DAO.

Chris Barber.


Dear All,

I tried the following scripts to call table and query in MS Access and
they worked.

Set rs1=OBJdbConn.Execute("select * from tableA")
Set rs1=OBJdbConn.Execute("execute Q1")
  where Q1 is the Query stored in MS Access

    However, when I applied them to call the Macro or Module in Ms
Access, I got error. Would you also advise how to call the Macro in
Excel. Thanks a lot.

Best Regards,
Stanley Ngai



Tue, 05 Oct 2004 18:37:30 GMT  
 Help - VB Script call MS Access
Added to Chris's nice recommandation.

Using getobject is easier,,

a=getobject("c:\x.mdb")
a.run "sub1"

If you want to do same for Excel(What you want really?)

a=getobject("c:\x.xls")
a.application.run "thisworkbook.sub1"

This is not tested because it is too manifest to test, but I can be wrong.

--
Han (Seoul, Korea)

Quote:
> Hmm.
> I had a good long search on the MSDN library documentation and came to the
> conclusion that for Macros and Module functions, you will have to use the
> COM Object Model.

> eg.

>     Set pobjAccess = CreateObject("Access.Application")

> With pobjAccess



> You are using an ADO through an OLEDB provider for Access?

> I believe that to call objects such as macros you may have to look at
using
> DAO, not ADO as a means of accessing the database.

> I'm not sure about accessing module functions through DAO.

> Chris Barber.



> Dear All,

> I tried the following scripts to call table and query in MS Access and
> they worked.

> Set rs1=OBJdbConn.Execute("select * from tableA")
> Set rs1=OBJdbConn.Execute("execute Q1")
>   where Q1 is the Query stored in MS Access

>     However, when I applied them to call the Macro or Module in Ms
> Access, I got error. Would you also advise how to call the Macro in
> Excel. Thanks a lot.

> Best Regards,
> Stanley Ngai



Tue, 05 Oct 2004 22:17:26 GMT  
 Help - VB Script call MS Access
Just a small point re: getObject or CreateObject.

getObject doesn't give you intellisense if your scripting in an environment
that supports it.

Intellisense can make it a lot easier to see where you're going if you're
not that familiar with the DOM.

Chris Barber.



Added to Chris's nice recommandation.

Using getobject is easier,,

a=getobject("c:\x.mdb")
a.run "sub1"

If you want to do same for Excel(What you want really?)

a=getobject("c:\x.xls")
a.application.run "thisworkbook.sub1"

This is not tested because it is too manifest to test, but I can be wrong.

--
Han (Seoul, Korea)

Quote:
> Hmm.
> I had a good long search on the MSDN library documentation and came to the
> conclusion that for Macros and Module functions, you will have to use the
> COM Object Model.

> eg.

>     Set pobjAccess = CreateObject("Access.Application")

> With pobjAccess



> You are using an ADO through an OLEDB provider for Access?

> I believe that to call objects such as macros you may have to look at
using
> DAO, not ADO as a means of accessing the database.

> I'm not sure about accessing module functions through DAO.

> Chris Barber.



> Dear All,

> I tried the following scripts to call table and query in MS Access and
> they worked.

> Set rs1=OBJdbConn.Execute("select * from tableA")
> Set rs1=OBJdbConn.Execute("execute Q1")
>   where Q1 is the Query stored in MS Access

>     However, when I applied them to call the Macro or Module in Ms
> Access, I got error. Would you also advise how to call the Macro in
> Excel. Thanks a lot.

> Best Regards,
> Stanley Ngai



Wed, 06 Oct 2004 07:17:49 GMT  
 Help - VB Script call MS Access
Chris, Han,
    Thank you very much for your advise. I can run it successfully now.
Quote:

> Just a small point re: getObject or CreateObject.

> getObject doesn't give you intellisense if your scripting in an environment
> that supports it.

> Intellisense can make it a lot easier to see where you're going if you're
> not that familiar with the DOM.

> Chris Barber.



> Added to Chris's nice recommandation.

> Using getobject is easier,,

> a=getobject("c:\x.mdb")
> a.run "sub1"

> If you want to do same for Excel(What you want really?)

> a=getobject("c:\x.xls")
> a.application.run "thisworkbook.sub1"

> This is not tested because it is too manifest to test, but I can be wrong.

> --
> Han (Seoul, Korea)


> > Hmm.
> > I had a good long search on the MSDN library documentation and came to the
> > conclusion that for Macros and Module functions, you will have to use the
> > COM Object Model.

> > eg.

> >     Set pobjAccess = CreateObject("Access.Application")

> > With pobjAccess



> > You are using an ADO through an OLEDB provider for Access?

> > I believe that to call objects such as macros you may have to look at
> using
> > DAO, not ADO as a means of accessing the database.

> > I'm not sure about accessing module functions through DAO.

> > Chris Barber.



> > Dear All,

> > I tried the following scripts to call table and query in MS Access and
> > they worked.

> > Set rs1=OBJdbConn.Execute("select * from tableA")
> > Set rs1=OBJdbConn.Execute("execute Q1")
> >   where Q1 is the Query stored in MS Access

> >     However, when I applied them to call the Macro or Module in Ms
> > Access, I got error. Would you also advise how to call the Macro in
> > Excel. Thanks a lot.

> > Best Regards,
> > Stanley Ngai



Fri, 08 Oct 2004 09:58:07 GMT  
 Help - VB Script call MS Access
Thanks for the reply - it's much appreciated.

It's incredible how many times we never get to hear if our responses helped
or even solved the problem.

Chris Barber.


Chris, Han,
    Thank you very much for your advise. I can run it successfully now.

Quote:

> Just a small point re: getObject or CreateObject.

> getObject doesn't give you intellisense if your scripting in an
environment
> that supports it.

> Intellisense can make it a lot easier to see where you're going if you're
> not that familiar with the DOM.

> Chris Barber.



> Added to Chris's nice recommandation.

> Using getobject is easier,,

> a=getobject("c:\x.mdb")
> a.run "sub1"

> If you want to do same for Excel(What you want really?)

> a=getobject("c:\x.xls")
> a.application.run "thisworkbook.sub1"

> This is not tested because it is too manifest to test, but I can be wrong.

> --
> Han (Seoul, Korea)


> > Hmm.
> > I had a good long search on the MSDN library documentation and came to
the
> > conclusion that for Macros and Module functions, you will have to use
the
> > COM Object Model.

> > eg.

> >     Set pobjAccess = CreateObject("Access.Application")

> > With pobjAccess



> > You are using an ADO through an OLEDB provider for Access?

> > I believe that to call objects such as macros you may have to look at
> using
> > DAO, not ADO as a means of accessing the database.

> > I'm not sure about accessing module functions through DAO.

> > Chris Barber.



> > Dear All,

> > I tried the following scripts to call table and query in MS Access and
> > they worked.

> > Set rs1=OBJdbConn.Execute("select * from tableA")
> > Set rs1=OBJdbConn.Execute("execute Q1")
> >   where Q1 is the Query stored in MS Access

> >     However, when I applied them to call the Macro or Module in Ms
> > Access, I got error. Would you also advise how to call the Macro in
> > Excel. Thanks a lot.

> > Best Regards,
> > Stanley Ngai



Fri, 08 Oct 2004 17:10:38 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. help: MS Access queries called from a VB front end

2. help: MS Access queries called from a VB front end

3. help: MS Access queries called from a VB front end

4. Please Help - Calling MS Access Caption Tag?

5. Please Help - Calling MS Access Caption Tag?

6. HELP on accessing Access DB in VB scripts !!

7. Recording Messages from Batch Programs in MS Access using VB Script

8. How to retrieve ID from MS Access database via VB script

9. Calling one VB Script from another VB Script

10. Please help!! with Python Script / MS-Access

11. Reading a MS Access table from VB script

12. MS Access v2 Pathworks function call Help !!!

 

 
Powered by phpBB® Forum Software