Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
Thad
DId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad
|||Hey Jens,
It worked! It was great help!
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in m
y
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>sql

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
--
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens Sü�meyer" wrote:
> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> > Hi all,
> > I am having problems importing data from Access 97 .mdb files into
> > my SQL Tables.
> > Using DTS, I invoked the Import/Export wizard.
> >
> > When prompt for the source, I specified Microsoft Access and it's
> > path/filename.
> > I did not enter username and password for the source.
> >
> > But when DTS executes the query to import data, it prompts me this error:
> > "Records cannot be read, no read permission on tablename"
> >
> > When i entered the userid and password for the workgroup file(mdw),
> > it says that "cannot start application. Workgroup information file is
> > missing or open exclusively"
> >
> > How can I work around this? Or Is there other way to import data into SQL
> > table? I tried to perform export from Access 97. It creates a new table in
> > my
> > SQL Server instead, without keys, indexes, relationship.
> >
> > Any thoughts/help greatky appreciated.
> >
> > --
> > With Thanks,
> > Thad
>
>

Importing from access

Hallo.

Is there a way to create a general dts to import tables from access mdb file?
so that i will not have to change the dts for every table i am adding to the access file?
something like foreachtable? or a way to read the tables list from the access file in the sql connection?

i am importing to sql server 2000.

thanksI'd just use a stored procedure. Have it take an argument for the linked server name for the access file (or a UNC pathname and have your procedure create the linked server too using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp)), then use sp_table_ex (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_9q60.asp) to put the catalog into a temp table, use a cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) to traverse the list of tables, and do a SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) from each of the foreign tables into a SQL Server table.

Then again, I'm lazy!

-PatP

Importing an Acess database file

Hi, I'm trying to do something which should be simple but isn't wotrking for
me. I need to import an Access.mdb file into SQL Server. I don't have Access
installed on my machine, just the mdb file.
From Import data I choose Microsoft Access, browse the file, choose windows
authentication, copy tables & views , but then it doesn't give me any tables
or views to choose from, just a blank page.
Hope someone can shed some light on this.
Thanks in advance
Ant
Hi,
It might be the jet version your using.
Tyr going to Microsoft and download the lastest jet service back. Sorry I
don't have the link tyr www.microsoft.com/download and search for JET
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B490308C-3886-4A94-9466-4F37561E848A@.microsoft.com...
> Hi, I'm trying to do something which should be simple but isn't wotrking
> for
> me. I need to import an Access.mdb file into SQL Server. I don't have
> Access
> installed on my machine, just the mdb file.
> From Import data I choose Microsoft Access, browse the file, choose
> windows
> authentication, copy tables & views , but then it doesn't give me any
> tables
> or views to choose from, just a blank page.
> Hope someone can shed some light on this.
> Thanks in advance
> Ant
|||how ur accessing the database

Importing an Acess database file

Hi, I'm trying to do something which should be simple but isn't wotrking for
me. I need to import an Access.mdb file into SQL Server. I don't have Access
installed on my machine, just the mdb file.
From Import data I choose Microsoft Access, browse the file, choose windows
authentication, copy tables & views , but then it doesn't give me any tables
or views to choose from, just a blank page.
Hope someone can shed some light on this.
Thanks in advance
AntHi,
It might be the jet version your using.
Tyr going to Microsoft and download the lastest jet service back. Sorry I
don't have the link tyr www.microsoft.com/download and search for JET
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B490308C-3886-4A94-9466-4F37561E848A@.microsoft.com...
> Hi, I'm trying to do something which should be simple but isn't wotrking
> for
> me. I need to import an Access.mdb file into SQL Server. I don't have
> Access
> installed on my machine, just the mdb file.
> From Import data I choose Microsoft Access, browse the file, choose
> windows
> authentication, copy tables & views , but then it doesn't give me any
> tables
> or views to choose from, just a blank page.
> Hope someone can shed some light on this.
> Thanks in advance
> Ant|||how ur accessing the database

Importing an Acess database file

Hi, I'm trying to do something which should be simple but isn't wotrking for
me. I need to import an Access.mdb file into SQL Server. I don't have Access
installed on my machine, just the mdb file.
From Import data I choose Microsoft Access, browse the file, choose windows
authentication, copy tables & views , but then it doesn't give me any tables
or views to choose from, just a blank page.
Hope someone can shed some light on this.
Thanks in advance
AntHi,
It might be the jet version your using.
Tyr going to Microsoft and download the lastest jet service back. Sorry I
don't have the link tyr www.microsoft.com/download and search for JET
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B490308C-3886-4A94-9466-4F37561E848A@.microsoft.com...
> Hi, I'm trying to do something which should be simple but isn't wotrking
> for
> me. I need to import an Access.mdb file into SQL Server. I don't have
> Access
> installed on my machine, just the mdb file.
> From Import data I choose Microsoft Access, browse the file, choose
> windows
> authentication, copy tables & views , but then it doesn't give me any
> tables
> or views to choose from, just a blank page.
> Hope someone can shed some light on this.
> Thanks in advance
> Ant|||how ur accessing the database

Importing Access databases into SQL Server

Hi there,

I have a situation where an application needs to import data from
number of access mdb files on a daily bases. The file names change
every day. The data import is very straight forward:

insert into sql_table select * from acess_table

There are up to 8 tables in each access file and some access files will
have less. So the process needs to figure out which tables exist in
Access mdb file and import them whole into sql staging tables.

Any recommendations are appreciated.

ThanksIt probably depends where you're running the load process from - that's
not really clear (to me) from your comments. If you push the data from
Access, then presumably it's not a problem, because you know which
tables are in each database. If you need to pull from MSSQL, and the
Access database names are always the same, then you could create linked
servers to each one, and get the data that way.

If the Access database names change, and you don't know in advance how
many tables there will be in each one, then you'll need something more
flexible. Personally, I would probably use DTS to connect to each
database, query the metadata to get the table names (although I don't
know exactly how to do that - perhaps an Access group could give more
details), and then load the data dynamically. Or write a tool in Perl,
C# or whatever to dynamically export and import the data via flat files
or ADO.

Finally, one other option would be to convert your Access databases to
ADPs, so you would have the data in MSSQL already. But this may not be
possible or desirable in your situation.

If this doesn't help, I suggest you post some more specific details of
what you need to do.

Simon|||How are these Access files being created daily
with different names and more importantly why?

What kind of bizarre methodology would require
different-named Access files on a daily basis?

GeoSynch

<boblotz2001@.yahoo.com> wrote in message
news:1112217371.433176.279470@.f14g2000cwb.googlegr oups.com...
> Hi there,
> I have a situation where an application needs to import data from
> number of access mdb files on a daily bases. The file names change
> every day. The data import is very straight forward:
> insert into sql_table select * from acess_table
> There are up to 8 tables in each access file and some access files will
> have less. So the process needs to figure out which tables exist in
> Access mdb file and import them whole into sql staging tables.
> Any recommendations are appreciated.
> Thanks

Importing Access database into Sql Server Express

Some facts:
1) I have an Access database (.mdb file) sitting on my harddrive.
2) I have Visual Studio 2005, Sql Server Express, and Sql Server
Management Studio Express.
3) I do *not* have Microsoft Access.
What I'm trying to do:
I simply want to import the Access database into Sql Server Express. In
other words, I want to end up with a Sql Server Express database that
has all the same tables, keys, and relationships as the Access database
as well as all the data from it. I can live without the queries stored
in the Access database, but those would be nice too.
What I've tried so far:
I'm able to connect to the Access database using the "Linked Servers"
features in Management Studio Express. From there, I was able to write
some simple Transact-SQL queries to find out what tables are in the
Access database and copy them, one at a time, into a Sql Server Express
database.
This is definitely a good start, but it doesn't take care of the
primary keys or foreign keys. There appear to be procedures for those
as well (sp_primarykeys, sp_foreignkeys), but I keep thinking there
must be an easier way.
Which brings me to...
Questions:
Without having to buy additional software/tools, can I import this
Access database without a lot of programming? If so, how?
Thanks in advance,
-DanDaniel
Actually I have not tried it by myself on SQL Server 2005, so try if this
works for you
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Daniel Manes" <danthman@.cox.net> wrote in message
news:1139881255.986395.191440@.f14g2000cwb.googlegroups.com...
> Some facts:
> 1) I have an Access database (.mdb file) sitting on my harddrive.
> 2) I have Visual Studio 2005, Sql Server Express, and Sql Server
> Management Studio Express.
> 3) I do *not* have Microsoft Access.
> What I'm trying to do:
> I simply want to import the Access database into Sql Server Express. In
> other words, I want to end up with a Sql Server Express database that
> has all the same tables, keys, and relationships as the Access database
> as well as all the data from it. I can live without the queries stored
> in the Access database, but those would be nice too.
> What I've tried so far:
> I'm able to connect to the Access database using the "Linked Servers"
> features in Management Studio Express. From there, I was able to write
> some simple Transact-SQL queries to find out what tables are in the
> Access database and copy them, one at a time, into a Sql Server Express
> database.
> This is definitely a good start, but it doesn't take care of the
> primary keys or foreign keys. There appear to be procedures for those
> as well (sp_primarykeys, sp_foreignkeys), but I keep thinking there
> must be an easier way.
> Which brings me to...
> Questions:
> Without having to buy additional software/tools, can I import this
> Access database without a lot of programming? If so, how?
> Thanks in advance,
> -Dan
>|||Thanks for the answer, Uri, but what I'm really looking for is a way to
take all the tables, primary keys, foreign keys, constraints and the
data itself from the Access (mdb) file and place them in a Sql Server
Express database.
But I did try your SELECT statement in SQL Server Express 2005. Doesn't
work.Seems like you need to set up the Access file as a Linked Server
then just do "SELECT * FROM Northwind...Customers."
But that only gives me the data, not the keys, constraints,
relationships, etc.
Any other ideas for importing the *whole* database?
Thanks,
-Dan