ADO ,MS Access and MS Excel

[Carles]

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

[Bob Phillips]

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

Carles <eexc...@hotmail.com> wrote in message
news:#mAFJDO...@cppssbbsa02.microsoft.com...

[Carles]

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

"Bob Phillips" <bob.ph...@freeuk.com> wrote in message
news:OnTnG8SXAHA.196@cppssbbsa05...

> 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
>
> Carles <eexc...@hotmail.com> wrote in message
> news:#mAFJDO...@cppssbbsa02.microsoft.com...

[Alex B]

first, learn to *not* crosspost.

"Carles" <eexc...@hotmail.com> wrote in message

news:ejzAC7UXAHA.196@cppssbbsa05...

[Jennifer Campion]

Carles <eexc...@hotmail.com> wrote in message
news:#mAFJDO...@cppssbbsa02.microsoft.com...

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