ADO ,MS Access and MS Excel 
Author Message
 ADO ,MS Access and MS Excel

Dear all

I have 2 computers. One is WInNT 4 Sever and One is Win98.
They are connected with different network.

The MS Access database is stored in NT machine.
What I want to do is to use Excel in Win98 machine to
extract data from the Access Database which is in NT machine.

In this project I use ADO to do that. I've tried to use System DSN, File
DSN, ODBC server....

How can I do that via the network?  It seems that I used many methods which
can be found in several text references but it didn't work. Pls help
Thx

Carles



Thu, 22 May 2003 12:34:28 GMT  
 ADO ,MS Access and MS Excel
Carles,

This seems straightforward.

First you need to create a DSN on the client machine that points to the
Access database.

Within Excel, you make the connections and access the tables just as you
would from script, using that named DSN.

I have not accessed Access from Excel, but I have accessed Access from ASP
and I have accessed Sybase from Excel, so I will do a test to link to Access
from Excel.

Send me your code if you want and I will see if I can help you resolve it.

Regards

Bob Phillips


Quote:
> Dear all

> I have 2 computers. One is WInNT 4 Sever and One is Win98.
> They are connected with different network.

> The MS Access database is stored in NT machine.
> What I want to do is to use Excel in Win98 machine to
> extract data from the Access Database which is in NT machine.

> In this project I use ADO to do that. I've tried to use System DSN, File
> DSN, ODBC server....

> How can I do that via the network?  It seems that I used many methods
which
> can be found in several text references but it didn't work. Pls help
> Thx

> Carles



Thu, 22 May 2003 03:00:00 GMT  
 ADO ,MS Access and MS Excel
Hi Bob

You mean I have to setup ODBC in "Control Panel" of the client machine
and let the DSN of ODBC point to the the Access Database in NT Sever, right?
sorry for this simple question because I'm still a beginner.
Thx
Carles


Quote:
> Carles,

> This seems straightforward.

> First you need to create a DSN on the client machine that points to the
> Access database.

> Within Excel, you make the connections and access the tables just as you
> would from script, using that named DSN.

> I have not accessed Access from Excel, but I have accessed Access from ASP
> and I have accessed Sybase from Excel, so I will do a test to link to
Access
> from Excel.

> Send me your code if you want and I will see if I can help you resolve it.

> Regards

> Bob Phillips



> > Dear all

> > I have 2 computers. One is WInNT 4 Sever and One is Win98.
> > They are connected with different network.

> > The MS Access database is stored in NT machine.
> > What I want to do is to use Excel in Win98 machine to
> > extract data from the Access Database which is in NT machine.

> > In this project I use ADO to do that. I've tried to use System DSN, File
> > DSN, ODBC server....

> > How can I do that via the network?  It seems that I used many methods
> which
> > can be found in several text references but it didn't work. Pls help
> > Thx

> > Carles



Fri, 23 May 2003 01:41:32 GMT  
 ADO ,MS Access and MS Excel
first, learn to *not* crosspost.


Quote:
> Hi Bob

> You mean I have to setup ODBC in "Control Panel" of the client machine
> and let the DSN of ODBC point to the the Access Database in NT Sever,
right?
> sorry for this simple question because I'm still a beginner.
> Thx
> Carles



> > Carles,

> > This seems straightforward.

> > First you need to create a DSN on the client machine that points to the
> > Access database.

> > Within Excel, you make the connections and access the tables just as you
> > would from script, using that named DSN.

> > I have not accessed Access from Excel, but I have accessed Access from
ASP
> > and I have accessed Sybase from Excel, so I will do a test to link to
> Access
> > from Excel.

> > Send me your code if you want and I will see if I can help you resolve
it.

> > Regards

> > Bob Phillips



> > > Dear all

> > > I have 2 computers. One is WInNT 4 Sever and One is Win98.
> > > They are connected with different network.

> > > The MS Access database is stored in NT machine.
> > > What I want to do is to use Excel in Win98 machine to
> > > extract data from the Access Database which is in NT machine.

> > > In this project I use ADO to do that. I've tried to use System DSN,
File
> > > DSN, ODBC server....

> > > How can I do that via the network?  It seems that I used many methods
> > which
> > > can be found in several text references but it didn't work. Pls help
> > > Thx

> > > Carles



Fri, 23 May 2003 03:00:00 GMT  
 ADO ,MS Access and MS Excel


Quote:
> Dear all

> I have 2 computers. One is WInNT 4 Sever and One is Win98.
> They are connected with different network.

> The MS Access database is stored in NT machine.
> What I want to do is to use Excel in Win98 machine to
> extract data from the Access Database which is in NT machine.

> In this project I use ADO to do that. I've tried to use System DSN, File
> DSN, ODBC server....

> How can I do that via the network?  It seems that I used many methods
which
> can be found in several text references but it didn't work. Pls help
> Thx

> Carles

Carles, you don't really need the DSN to do this.

Here's an example that might get you started:

Sub ADOExample()
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set con = New ADODB.Connection
    With con
        .Provider = "Microsoft.Jet.OLEDB.3.51"
        .ConnectionString = "\\Vbstudent03\C\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb"
        .Open
    End With

    Set rst = New ADODB.Recordset

    rst.Open "Select * from Products", con, adOpenDynamic

    'If you are using Excel 2000, you can use the .CopyFromRecordset method
to dump the data into Excel:

    Range("A2").CopyFromRecordset rst

    'If you are not using xl2000, you can loop through the recordset to put
the data into a worksheet.

End Sub

Hope that helps...

Jennifer A. Campion
Microsoft MVP



Sat, 24 May 2003 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. ADO ,MS Access and MS Excel

2. Ms Excel and Ms Access Problem

3. Script MS-Access Import of MS-Excel?

4. MS ACCESS data to MS EXCEL directly

5. looking for conversion software for MS Excel 4.0 (ELM) to MS Excel 5.0 visual Basic

6. Using ADO with MS-Access and Excel 2000

7. Accessing current record in MS Access with DDE from MS Word

8. Regarding Accessing External database using MS-Access and MS-Visual Basic

9. Link MS Access on PocketPC to Desktop MS Access

10. data exchange between MS Excel and MS Project

11. Problems with installing MS Word8.0 and MS Excel 8.0 Object library

12. Export to MS Word & MS Excel problems

 

 
Powered by phpBB® Forum Software