Conversion of Access application to SQL Server

Hi there,

I have written an application which uses MS Access for it's database engine.
Due to the large size which the database has become I have decided that it
would be sensible to use SQL Server with the application instead.

I am an extreme SQL Server newbie so I am not really sure what I'm doing
yet! I have successfully downloaded and installed the MS SQLDE 2000 and
service pack 3.

What do I need to do next? Ideally I would like to convert the existing
Access database to MS SQL Server format. Also I would like to know if it is
possible to create an SQL Server database from scratch using a gui
environment similar to Access and if so which software (preferably free) do
I need to achieve this?

Many thanks,
Clive.The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.|||The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.|||"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.

I believe that Access has an upsizing wizard which attempts to
automatically upgrade Access applications to MSSQL, although like all
platform migration tools it probably has a number of limitations. In
any case, you may get a better response to this in an Access newgroup.

http://www.aspfaq.com/show.asp?id=2182

As for GUIs for MSDE, see here:

http://www.aspfaq.com/show.asp?id=2442

Simon

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

constructing varchar computed column , using bit fields

Hi,
Due to a certain circumstance i have to add a field to my table, which would be a varchar type. this varchar contructs istfel using 4 fields of type bit

so if the table def goes as
isA(bit), isB(bit), isC(bit), isD(bit), ABCD(varchar(4))

the value of the field ABCD should be constructued from the 4 bit fields
if isA,isC is checked then ABCD should be AC
if isA,isB,isD is checked then ABCD should be ABD
is there a way i could achieve it using computed columns or would i have to make a trigger..(which i am also not sure on how to go about it)

Sorry for the trouble, but i couldnt get my head around with starting it either !

thankyouHi,
Updating on what i have done
Created a trigger on insert,update
and with a series of IF statements i contructed the varchar field based on if the bit fields were set to true.

consolidation of datafiles

i'm fairly new at this. can someone help.

i inherited a server where people have been throwing datafiles/indexfiles/logfiles all over the place due to space limitations. i've been approved for more space on the server and am trying to reorganize the filesystem. can someone tell me or point me in the right direction...

how to consolidate multiple log files (i understand having more then 1 log file is completely useless)

how to move datafiles to different drives

how to consolidate multiple datafiles... etc.

any help would be greatly appreciated.

Look in Books Online about using [ DBCC SHRINKFILE ], especially with the EMPTYFILE option.

Moving datafiles is as simple as DETACH thedatabase, copy the files to new location, and the ATTACH the database. You can use the GUI in EM/SSMS, or sp_detach_db and sp_attach_db. The database will be 'off line' as long as it takes to copy the files and re-attach.