copy from sql to access

I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegroups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>

copy from sql to access

I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!
To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegro ups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
sqlsql

copy from sql to access

I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegroups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>

Copy file on network

I have a job which call a SP and that SP generates a excel file dump using
bcp. Now form my website i want to access that excel file. Both my webserver
and db server is on different meachine.
How can i achive this?
can BCP generate output on network. Or can i copy generated file to
webserver using some command through SP ?Vikram
If I understood properly , you can create a linked server to EXCEL file.
HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"Vikram" <aa@.aa> wrote in message
news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
>I have a job which call a SP and that SP generates a excel file dump using
> bcp. Now form my website i want to access that excel file. Both my
> webserver
> and db server is on different meachine.
> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
>
>|||But in linked server i have to have excel file. But i do not want this, i
want to have excel file generated by bcp...
any other way ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> Vikram
> If I understood properly , you can create a linked server to EXCEL file.
> HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
>
> "Vikram" <aa@.aa> wrote in message
> news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
using
>|||DTS?
"Vikram" <aa@.aa> wrote in message
news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> But in linked server i have to have excel file. But i do not want this, i
> want to have excel file generated by bcp...
> any other way ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> using
>|||NO I am suing BCP , calling it from sp using xp_cmdshell
I dont want to use DTS as sp whic return data use temp table and also i have
many sp whose data i have to export to excel
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2370krlPPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> DTS?
>
> "Vikram" <aa@.aa> wrote in message
> news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
i
file.
>|||> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
UNC path depending on your permissions.
EXEC master..xp_cmdshell "bcp DB.dbo.Table out
\\SERVER1\Share\test.xls -c -T"
ftp via xp_cmdshell could work.

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a database
from within the server? It will be only a copy of the structure, no data at
all. Thanks
For SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generate
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:

> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure and
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a database
> from within the server? It will be only a copy of the structure, no data at
> all. Thanks
sqlsql

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a databas
e
from within the server? It will be only a copy of the structure, no data at
all. ThanksFor SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generat
e
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:

> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure a
nd
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a datab
ase
> from within the server? It will be only a copy of the structure, no data a
t
> all. Thanks

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a database
from within the server? It will be only a copy of the structure, no data at
all. ThanksFor SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generate
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:
> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure and
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a database
> from within the server? It will be only a copy of the structure, no data at
> all. Thanks

Copy Data from Access to SQL Express in Code?

I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine. I can use, compact and copy etc in MS Access without problem.

With the upsize, I get No errors, just does not reliably move ALL data. I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.

========================================

So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.

I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables.

1 questions and one "bug" report need solutions to both.

Question: How can I copy the value from the MS access autonumber field to the identity field. (Some of the autonumber rows have been deleted so not always sequentiall) (I can do this moving data in code between to MS access databases that have autonumber fields)

=========================

I tied an elaborate write around to addNEW and then read the data in the newly created row. Reading the Identity field, I look up that number in the MS access database. Then I copy the ACCESS data to the Newly created table row in SQL Exress and update.

It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value. Makes it impossible to make sure all of my Access rows get copied. Tried transactions but not supported with the ADODB configuration, but doubt that would help.

Example data returned in the SQL Express recordset, when I read the Identify field back I get

1,2,3 etc 31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.

The only info I found was that it could be a cursorlocation problem, what do you recommend?

?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?

As you are just loading the data I would set the field in the SQL Express system to just an int then load the data back in. Once complete change the filed back to an identity field and start the numbers off on the next availible number.

|||

I tried that, but it will NOT let me set the identity field. It generates it.

It works with Access but NOT SQL EXPRESS, if I hit the ADDNEW and then set the identify field and update it errors.

|||

Hey TurboDoc,

You should be able to insert data into your Identity field by setting the IDENTITY_INSERT option. Check out SQL BOL for more information on that. I think that will be more straight forward than trying to create blank records in SQL and then matching up the Identity values.

Hope this helps,

Mike

Copy and Paste from access into columns

Is there a way to copy and paste data into columns when view the table columns?

I would like to be able to copy and paste data from an access database.

TIA

I don't believe SSMSE supports this functionality. If you need to move data from Access (Jet) to SQL Server, you have a number of choices:

Export the data from within Access.|||Thanks Mike.

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy a Table

In MS Access, if I want to copy a table, I can just highlight it, hit ctrl-c and ctrl-v, and VOILA! I now have a "copy of MyTable" pasted, with all data duplicated.

Um. How do I do the same thing in SQL Server 2005? Can this be done with Server Management Studio? I don't necessarily want to copy relationships to other tables, I just want a stand-alone copy of the table, with a new name, inside the same database.

(EDIT): I just noticed I can achieve this outcome with:

SELECT * INTO TestIT FROM TestCases

...and while that is simple enough to get the job done, I kinda still wonder if there is a GUI-oriented means to accomplish this (like Access does), without writing bits of SQL as above.

the closest function of SQL Mgmt. Studio to what you are describing is the import/export wizard (right-click on a a database in object explorer and select tasks/[import or export]. But I dont think it will allow you to specify the same source and destination (you may want "play" with the wizard a bit to ensure you cannot get it to work for you). Outside of that...NO. A related features is that of scripting data, so you could right-click a table and select Script Table As/"Generate Data", I believe SQL tools will have this feature in the future, but I would think other third-party tools already do this, not sure though if this can be done via the SMO model yet or not...

Cookie problem in reporting services

Hi, I'm using URL access to view my reports from within my website. My report
is being viewed from within an IFrame on the site. IE 6 with default security
blocks cookies coming from the IFrame. Apparently - setting
"UseSessionCookies" to false in the site settings is supposed to cause RS to
manage session via the querystring and instead. I have done this and RS is
still sending cookies? (I am running SP1)
Is this a bug is RS or am i missing something?Hi Jarrod,
From your descriptions, I understood cookies are continuing sending even
you have set UseSessionCookies to false. Have I understood you? Correct me
if I was wrong.
Based on my scope, UseSessionCookies is a system property and you could set
that to false by using the SOAP API SetSystemProperties method.
I am not sure why it is continuing sending cookies as you said. I have
surveied known issues internally and do not find anything related with
UseSessionCookies so that is it possible for you to generate a samll sampel
for me to reproduce it?
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jarrod,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000
.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead
of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the applicati
on
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
Folke
There are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke
|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:
> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
> > There are no such things as "forms" in SQL Server; this is all handled by
> > external applications.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "folke" <folke@.discussions.microsoft.com> wrote in message
> > news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > > We have an application in Access 2003. The user interface is OK (Forms
> > > created with Access) but we're lacking a "real" database i.e SQL Server
> > 2000.
> > > Any hints on how to move the database without having to rewrite the user
> > > interface? We could rewrite the Forms in C# but time is not on our side!
> > > --
> > > Folke
> >
> >
> >
>

Converting to MSDE - stuck already!

I just installed MSDE on my desktop and for my first project took a copy of
one of my Access 2000 databases I have used for years and upsized it. There
were many errors, especially in the queries/views. I expected some errors.
This personal database completely resides on my PC, tables and all. When I
upsized it, it copied the tables over empty - no data. So I copied and
pasted data into the Switchboard table and others.
Then I tried to open the Switchboard form that was created automatically
when I first created the database.
This was the first programming error I encountered.
Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 8
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL) 'XXXXX error message occurs here
XXXXX
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard
page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If
' Close the recordset and the database.
rst.Close
dbs.Close
End Sub
I get a run time error 91, object variable or With block variable not set.
Help says for error 91: First you must declare the object variable. Then you
must assign a valid reference to the object variable using the Set
statement.
I have declared and set both the database and recordset.
Since I am totally new to MSDE, can anyone guide me to where I can find out
more about this and other errors I am sure to encounter?
Thanks,
Mich
I suspect that your problem is occurring with the statement
Set dbs = CurrentDb()
How are you connecting from Access to the MSDE tables?
Chuck Heinzelman - MCSD, MCDBA
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server and its community of
SQL Server professionals.
www.sqlpass.org
"M Skabialka" <mskabialka@.NOSPAMdrc.com> wrote in message
news:uGv1gzqGFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I just installed MSDE on my desktop and for my first project took a copy
of
> one of my Access 2000 databases I have used for years and upsized it.
There
> were many errors, especially in the queries/views. I expected some
errors.
> This personal database completely resides on my PC, tables and all. When
I
> upsized it, it copied the tables over empty - no data. So I copied and
> pasted data into the Switchboard table and others.
> Then I tried to open the Switchboard form that was created automatically
> when I first created the database.
> This was the first programming error I encountered.
> Private Sub FillOptions()
> ' Fill in the options for this switchboard page.
> ' The number of buttons on the form.
> Const conNumButtons = 8
> Dim dbs As Database
> Dim rst As Recordset
> Dim strSQL As String
> Dim intOption As Integer
> ' Set the focus to the first button on the form,
> ' and then hide all of the buttons on the form
> ' but the first. You can't hide the field with the focus.
> Me![Option1].SetFocus
> For intOption = 2 To conNumButtons
> Me("Option" & intOption).Visible = False
> Me("OptionLabel" & intOption).Visible = False
> Next intOption
> ' Open the table of Switchboard Items, and find
> ' the first item for this Switchboard Page.
> Set dbs = CurrentDb()
> strSQL = "SELECT * FROM [Switchboard Items]"
> strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
> Me![SwitchboardID]
> strSQL = strSQL & " ORDER BY [ItemNumber];"
> Set rst = dbs.OpenRecordset(strSQL) 'XXXXX error message occurs
here
> XXXXX
> ' If there are no options for this Switchboard Page,
> ' display a message. Otherwise, fill the page with the items.
> If (rst.EOF) Then
> Me![OptionLabel1].Caption = "There are no items for this
switchboard
> page"
> Else
> While (Not (rst.EOF))
> Me("Option" & rst![ItemNumber]).Visible = True
> Me("OptionLabel" & rst![ItemNumber]).Visible = True
> Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
> rst.MoveNext
> Wend
> End If
> ' Close the recordset and the database.
> rst.Close
> dbs.Close
> End Sub
> I get a run time error 91, object variable or With block variable not set.
> Help says for error 91: First you must declare the object variable. Then
you
> must assign a valid reference to the object variable using the Set
> statement.
> I have declared and set both the database and recordset.
> Since I am totally new to MSDE, can anyone guide me to where I can find
out
> more about this and other errors I am sure to encounter?
> Thanks,
> Mich
>

Converting To ADP, Reference Recommendations

I have been creating Access applications since version 2.0 and may soon
become involved with a project that will convert an existing database
into an ADP. I would consider myself verbose in VBA, but inexperienced
using a SQL server back end. Typically when faced with a new challenge
like this one, I will read technical articles & books plus start
experimenting with code. I am sure there are many of you out there
that understand the issues I will have to confront (it worked in
'just' Access, but now...). I would appreciate recommendations
regarding books, web page links, etc., that will be helpful in my next
project.
Access version 2000 - possibly moving to 2003 next year.
Regards,
Bob<boborta@.hotmail.com> wrote in message
news:1162223068.352845.97040@.i42g2000cwa.googlegroups.com...
> I have been creating Access applications since version 2.0 and may soon
> become involved with a project that will convert an existing database
> into an ADP. I would consider myself verbose in VBA, but inexperienced
> using a SQL server back end. Typically when faced with a new challenge
> like this one, I will read technical articles & books plus start
> experimenting with code. I am sure there are many of you out there
> that understand the issues I will have to confront (it worked in
> 'just' Access, but now...). I would appreciate recommendations
> regarding books, web page links, etc., that will be helpful in my next
> project.
> Access version 2000 - possibly moving to 2003 next year.
> Regards,
Keep in mind that you can use SQL Server as a back-end and continue to use
an access mdb as the front-end. Many developers use this model. You can
still use SQL Server functionality like views, stored procedures, etc.. I
have had difficulties with ADPs and no longer attempt to use them. I also
have questions about the future of ADPs. Others have different views. Google
groups for "access adp pros cons" and you'll see there has been a lot of
discussion on this. Perhaps it has already been decided that this project
will be ADP. If so then good luck.
If the requirement is to move the data to SQL Server but the format of the
front-end is still open I would generally recommend using mdb with linked
tables to SQL Server. A lot would depend on why you are upgrading, the
application itself, the experience of the developers, etc. This is all of
course in my opinion.|||Try the book:
Microsoft Access Developers Guide to SQL Server
by Mary Chipman and Andy Baron. They address a lot of the
issues you will face in the conversion from an Access
developers perspective.
-Sue
On 30 Oct 2006 07:44:28 -0800, "boborta@.hotmail.com"
<boborta@.hotmail.com> wrote:

>I have been creating Access applications since version 2.0 and may soon
>become involved with a project that will convert an existing database
>into an ADP. I would consider myself verbose in VBA, but inexperienced
>using a SQL server back end. Typically when faced with a new challenge
>like this one, I will read technical articles & books plus start
>experimenting with code. I am sure there are many of you out there
>that understand the issues I will have to confront (it worked in
>'just' Access, but now...). I would appreciate recommendations
>regarding books, web page links, etc., that will be helpful in my next
>project.
>Access version 2000 - possibly moving to 2003 next year.
>Regards,
>Bob

Converting To ADP, Reference Recommendations

I have been creating Access applications since version 2.0 and may soon
become involved with a project that will convert an existing database
into an ADP. I would consider myself verbose in VBA, but inexperienced
using a SQL server back end. Typically when faced with a new challenge
like this one, I will read technical articles & books plus start
experimenting with code. I am sure there are many of you out there
that understand the issues I will have to confront (it worked in
'just' Access, but now...). I would appreciate recommendations
regarding books, web page links, etc., that will be helpful in my next
project.
Access version 2000 - possibly moving to 2003 next year.
Regards,
Bob<boborta@.hotmail.com> wrote in message
news:1162223068.352845.97040@.i42g2000cwa.googlegroups.com...
> I have been creating Access applications since version 2.0 and may soon
> become involved with a project that will convert an existing database
> into an ADP. I would consider myself verbose in VBA, but inexperienced
> using a SQL server back end. Typically when faced with a new challenge
> like this one, I will read technical articles & books plus start
> experimenting with code. I am sure there are many of you out there
> that understand the issues I will have to confront (it worked in
> 'just' Access, but now...). I would appreciate recommendations
> regarding books, web page links, etc., that will be helpful in my next
> project.
> Access version 2000 - possibly moving to 2003 next year.
> Regards,
Keep in mind that you can use SQL Server as a back-end and continue to use
an access mdb as the front-end. Many developers use this model. You can
still use SQL Server functionality like views, stored procedures, etc.. I
have had difficulties with ADPs and no longer attempt to use them. I also
have questions about the future of ADPs. Others have different views. Google
groups for "access adp pros cons" and you'll see there has been a lot of
discussion on this. Perhaps it has already been decided that this project
will be ADP. If so then good luck.
If the requirement is to move the data to SQL Server but the format of the
front-end is still open I would generally recommend using mdb with linked
tables to SQL Server. A lot would depend on why you are upgrading, the
application itself, the experience of the developers, etc. This is all of
course in my opinion.|||Try the book:
Microsoft Access Developers Guide to SQL Server
by Mary Chipman and Andy Baron. They address a lot of the
issues you will face in the conversion from an Access
developers perspective.
-Sue
On 30 Oct 2006 07:44:28 -0800, "boborta@.hotmail.com"
<boborta@.hotmail.com> wrote:
>I have been creating Access applications since version 2.0 and may soon
>become involved with a project that will convert an existing database
>into an ADP. I would consider myself verbose in VBA, but inexperienced
>using a SQL server back end. Typically when faced with a new challenge
>like this one, I will read technical articles & books plus start
>experimenting with code. I am sure there are many of you out there
>that understand the issues I will have to confront (it worked in
>'just' Access, but now...). I would appreciate recommendations
>regarding books, web page links, etc., that will be helpful in my next
>project.
>Access version 2000 - possibly moving to 2003 next year.
>Regards,
>Bob

Converting Stored Procedures back to MS Access Queries

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.