Trying to read Access97 files from SQL7

Trying to read Access97 files from SQL7

Post by Bo Berglu » Mon, 13 Nov 2000 04:00:00



I have this problem:
There are a number of Access97 mdb files I need to read from SQL7.
(Need to move data into SQL7).

The files are protected by a workgroup mdw file and user logins with
varying permissions. I have created a SQL7 login which maps directly
to one of these users (username, password), then I have tried using
the syntax of Books On Line section "ole db provider for Jet" but as
soon as I try to read these files I get an error saying that the
workgroup file cannot be found or is opened exclusively by another
user...
Why is this so? The file sits next to the default system.mdw in
c:\winnt\system32 in my PC (W2000 professional). And I am the only one
using my PC right now...
I have also tried the registry hack suggested in BOL with and without
full path to the workgroup file, but no go.
I have executed sp_addlinkedserver and sp_addlinkedsrvlogin also, but
the same problem sufaces always. :-(

What am I missing in this sentence:

select a.*
from openrowset(
'Microsoft.Jet.OLEDB.4.0',
'c:\path\test.mdb';'username';'password',tablename) as a

If I test this syntax towards the employee.mdb that ships with
MSOffice97 and use username=admin, password='' then it returns rows so
the basic syntax seems OK.
It is almost as if the OLEDB driver is not connected to my workgroup
file (as says the error message). But how can I specify it better?
Mybe someone can give the details of the complete procedure to do this
step-by-step?

Bo Berglund


PGP: My public key is available at the following locations:
Idap://certserver.pgp.com
http://pgpkeys.mit.edu:11371

 
 
 

Trying to read Access97 files from SQL7

Post by BP Margoli » Mon, 13 Nov 2000 04:00:00


Bo,

Not sure, but it sounds to me like you are overlooking that when you ask SQL
Server to read from an Access database, that the drive mapping must be from
the SQL Server box, not from your PC. Remember that the query is executing
on SQL Server ... it is not executing on your PC ... so the drive mapping
must be the drive mapping as seen from the SQL Server box.

General best practice in all such cases is to avoid drive letter mappings
entirely and stick to UNC's. Also make sure that the account that SQL Server
is running under has operating system permissions to the Access database ...
for example, if you want SQL Server to see a file on another box, you do NOT
want to be running SQL Server under the Local System Account.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I have this problem:
> There are a number of Access97 mdb files I need to read from SQL7.
> (Need to move data into SQL7).

> The files are protected by a workgroup mdw file and user logins with
> varying permissions. I have created a SQL7 login which maps directly
> to one of these users (username, password), then I have tried using
> the syntax of Books On Line section "ole db provider for Jet" but as
> soon as I try to read these files I get an error saying that the
> workgroup file cannot be found or is opened exclusively by another
> user...
> Why is this so? The file sits next to the default system.mdw in
> c:\winnt\system32 in my PC (W2000 professional). And I am the only one
> using my PC right now...
> I have also tried the registry hack suggested in BOL with and without
> full path to the workgroup file, but no go.
> I have executed sp_addlinkedserver and sp_addlinkedsrvlogin also, but
> the same problem sufaces always. :-(

> What am I missing in this sentence:

> select a.*
> from openrowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'c:\path\test.mdb';'username';'password',tablename) as a

> If I test this syntax towards the employee.mdb that ships with
> MSOffice97 and use username=admin, password='' then it returns rows so
> the basic syntax seems OK.
> It is almost as if the OLEDB driver is not connected to my workgroup
> file (as says the error message). But how can I specify it better?
> Mybe someone can give the details of the complete procedure to do this
> step-by-step?

> Bo Berglund


> PGP: My public key is available at the following locations:
> Idap://certserver.pgp.com
> http://pgpkeys.mit.edu:11371


 
 
 

Trying to read Access97 files from SQL7

Post by Bo Berglu » Mon, 13 Nov 2000 04:00:00


Thanks for your view,
but it does not apply here because I AM running SQL7 on *my own* NT WS
machine. It is a development environment where I am trying to port an
application from using Access97 database files (via DAO) to SQL7 using
ADO.
And since my Access97 files contain a lot of data for testing I need
to move them over using a connection through OLEDB to the Access file.
I am using Query Analyzer and the Acces97 file is on my PC as is the
OLEDB system driver and the workgroup file.
I am logged in to my machine on an admin account and I am using sa as
the SQL7 login for these initial tests.

So I should be fine but I am not.

/Bo

On Sun, 12 Nov 2000 09:50:31 -0500, "BP Margolin"


>Bo,

>Not sure, but it sounds to me like you are overlooking that when you ask SQL
>Server to read from an Access database, that the drive mapping must be from
>the SQL Server box, not from your PC. Remember that the query is executing
>on SQL Server ... it is not executing on your PC ... so the drive mapping
>must be the drive mapping as seen from the SQL Server box.

>General best practice in all such cases is to avoid drive letter mappings
>entirely and stick to UNC's. Also make sure that the account that SQL Server
>is running under has operating system permissions to the Access database ...
>for example, if you want SQL Server to see a file on another box, you do NOT
>want to be running SQL Server under the Local System Account.

>----------------------------------------------------------------
>BP Margolin
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



>> I have this problem:
>> There are a number of Access97 mdb files I need to read from SQL7.
>> (Need to move data into SQL7).

>> The files are protected by a workgroup mdw file and user logins with
>> varying permissions. I have created a SQL7 login which maps directly
>> to one of these users (username, password), then I have tried using
>> the syntax of Books On Line section "ole db provider for Jet" but as
>> soon as I try to read these files I get an error saying that the
>> workgroup file cannot be found or is opened exclusively by another
>> user...
>> Why is this so? The file sits next to the default system.mdw in
>> c:\winnt\system32 in my PC (W2000 professional). And I am the only one
>> using my PC right now...
>> I have also tried the registry hack suggested in BOL with and without
>> full path to the workgroup file, but no go.
>> I have executed sp_addlinkedserver and sp_addlinkedsrvlogin also, but
>> the same problem sufaces always. :-(

>> What am I missing in this sentence:

>> select a.*
>> from openrowset(
>> 'Microsoft.Jet.OLEDB.4.0',
>> 'c:\path\test.mdb';'username';'password',tablename) as a

>> If I test this syntax towards the employee.mdb that ships with
>> MSOffice97 and use username=admin, password='' then it returns rows so
>> the basic syntax seems OK.
>> It is almost as if the OLEDB driver is not connected to my workgroup
>> file (as says the error message). But how can I specify it better?
>> Mybe someone can give the details of the complete procedure to do this
>> step-by-step?

Bo Berglund


PGP: My public key is available at the following locations:
Idap://certserver.pgp.com
http://pgpkeys.mit.edu:11371

 
 
 

Trying to read Access97 files from SQL7

Post by mary chipma » Mon, 13 Nov 2000 04:00:00


SQL Server 7 does not deal very well with secured Access mdb's. you
can set up a linked server to a secured Access database as long as you
create the appropriate registry entries. however, you can only query
databases from that one mdw. if you have multiple workgroup files, as
most people do, you're SOL. the other odd thing is that the registry
location you need to write to is not the same one used by the
workgroup administrator (wrkgadm.exe). i never tested using a secured
mdb with openrowset -- that may be the problem. try using a linked
server with the registry settings and see if that works. you'll be
able to tell right away by running the sp_tables_ex against the linked
server name. if you get an error, then the linked server or the
registry key was not configured correctly.

bottom line is, you're better off not using Jet security if you need
to query your Access databases from SQL Server. it's a giant PITA, and
has gotten no better in SQL Server 2000.



>Thanks for your view,
>but it does not apply here because I AM running SQL7 on *my own* NT WS
>machine. It is a development environment where I am trying to port an
>application from using Access97 database files (via DAO) to SQL7 using
>ADO.
>And since my Access97 files contain a lot of data for testing I need
>to move them over using a connection through OLEDB to the Access file.
>I am using Query Analyzer and the Acces97 file is on my PC as is the
>OLEDB system driver and the workgroup file.
>I am logged in to my machine on an admin account and I am using sa as
>the SQL7 login for these initial tests.

>So I should be fine but I am not.

>/Bo

>On Sun, 12 Nov 2000 09:50:31 -0500, "BP Margolin"

>>Bo,

>>Not sure, but it sounds to me like you are overlooking that when you ask SQL
>>Server to read from an Access database, that the drive mapping must be from
>>the SQL Server box, not from your PC. Remember that the query is executing
>>on SQL Server ... it is not executing on your PC ... so the drive mapping
>>must be the drive mapping as seen from the SQL Server box.

>>General best practice in all such cases is to avoid drive letter mappings
>>entirely and stick to UNC's. Also make sure that the account that SQL Server
>>is running under has operating system permissions to the Access database ...
>>for example, if you want SQL Server to see a file on another box, you do NOT
>>want to be running SQL Server under the Local System Account.

>>----------------------------------------------------------------
>>BP Margolin
>>Please reply only to the newsgroups.
>>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>>can be cut and pasted into Query Analyzer is appreciated.



>>> I have this problem:
>>> There are a number of Access97 mdb files I need to read from SQL7.
>>> (Need to move data into SQL7).

>>> The files are protected by a workgroup mdw file and user logins with
>>> varying permissions. I have created a SQL7 login which maps directly
>>> to one of these users (username, password), then I have tried using
>>> the syntax of Books On Line section "ole db provider for Jet" but as
>>> soon as I try to read these files I get an error saying that the
>>> workgroup file cannot be found or is opened exclusively by another
>>> user...
>>> Why is this so? The file sits next to the default system.mdw in
>>> c:\winnt\system32 in my PC (W2000 professional). And I am the only one
>>> using my PC right now...
>>> I have also tried the registry hack suggested in BOL with and without
>>> full path to the workgroup file, but no go.
>>> I have executed sp_addlinkedserver and sp_addlinkedsrvlogin also, but
>>> the same problem sufaces always. :-(

>>> What am I missing in this sentence:

>>> select a.*
>>> from openrowset(
>>> 'Microsoft.Jet.OLEDB.4.0',
>>> 'c:\path\test.mdb';'username';'password',tablename) as a

>>> If I test this syntax towards the employee.mdb that ships with
>>> MSOffice97 and use username=admin, password='' then it returns rows so
>>> the basic syntax seems OK.
>>> It is almost as if the OLEDB driver is not connected to my workgroup
>>> file (as says the error message). But how can I specify it better?
>>> Mybe someone can give the details of the complete procedure to do this
>>> step-by-step?

>Bo Berglund


>PGP: My public key is available at the following locations:
>Idap://certserver.pgp.com
>http://pgpkeys.mit.edu:11371

 
 
 

1. Interbase: I/O error for file KATALOG.GDB - Error while trying to read from file

Problem:

I use Delphi 2.0 (C/S Suite) and the local interbase server. My
application use a database KATALOG.GDB stored on the local harddisk.
Filesize is about 1,1MB. After a reboot I'm not able to connect to the
database file. After the login to the server (SYSDBA, MASTERKEY) I try
to connect to the database with the following result:

   I/O error for file "D:\KATALOG.GDB"
   - Error while trying to read from file
   - Der Vorgang wurde ausgefuehrt

I tried to repair the database with GFIX.EXE (interbase\bin directory)
with the same result. I tried the "-ignore" switch (to ignore the
checksum of the database) - no result.

I need the access to this database! What's the way to repair it? Perhaps
a combination of switchs or calls with the GFIX program?

/Stefan

2. DB2 SQL stored procedures - Examples

3. Help!!!Cannt read localization Mdb files(Use Access97 and VB5)

4. Can you "select for update" if joining tables?

5. VB4.0 reading Access97 files

6. Problem with Cursors and SQL6.5 (sp2)

7. Can VFP read and write to access97 files?

8. SQL Error Message

9. Reading Btreive files to Access97

10. Trying to import logical files from AS400 into SQL7

11. Using SQL 6.5 to try to read .dat files

12. Beginner trying to read Visual Foxpro Database with CDX index and Fpt file