Converting Access.mdb(2002SP3) to Sql2005

[Jos]

Hi,

My boss wants me to convert an Access.mdb coding to Report Sql 2005.
The Access.mdb accesses Sql 2005 databases via ODBC more than one Sql 2005
databases (read-only DNS) and primarely produces reports or Excel sheets
based on tables and views in these databases, using forms, queries and
modules and some small local access tables in the .mdb.

I tried to use the upsizing wizard on my test.mdb.
First it tried to download all ODBC-linked tables into the new .adp file,
which is impossible, because of disk size restrictions and unwanted, because
the ODBC-linked tables are read-only for the .mdb.
Then it moved all local .mdb tables to .adp file and ODBC'ed the local files
to the .adp. The ODBC-tables in the orginal .mdb are empty in the .adp as
well as in the old .mdb.
Appearently I am doing something wrong, what can it be??

[Alex Dybenko]

Hi,
not so clear what are you trying to get. "Access.mdb accesses Sql 2005
databases via ODBC" - this is already upsized application, as data on SQL
server database.
If you want to use SQL Server reporting services - no sure that you can
somehow convert access to it
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Jos
Hi,
My boss wants me to convert an Access.mdb coding to Report Sql 2005.
The Access.mdb accesses Sql 2005 databases via ODBC more than one Sql 2005
databases (read-only DNS) and primarely produces reports or Excel sheets
based on tables and views in these databases, using forms, queries and
modules and some small local access tables in the .mdb.
I tried to use the upsizing wizard on my test.mdb.
First it tried to download all ODBC-linked tables into the new .adp file,
which is impossible, because of disk size restrictions and unwanted, because
the ODBC-linked tables are read-only for the .mdb.
Then it moved all local .mdb tables to .adp file and ODBC'ed the local files
to the .adp. The ODBC-tables in the orginal .mdb are empty in the .adp as
well as in the old .mdb.
Appearently I am doing something wrong, what can it be??
--
Jos

[John W. Vinson]

Post by Jos
My boss wants me to convert an Access.mdb coding to Report Sql 2005.

I'm pretty sure you will need to a) just leave the data in SQL/Server where it
is already; the upsizing wizard is for moving data from an Access JET database
into SQL, so it's not needed here; and b) scrap the entire Access database
frontend and recreate its functionality from scratch in SQL Reporting
Services. They are *very* different programming environments and there is, to
my knowledge, no way to convert an Access Report into a SQL Reporting Services
report. Your boss may be asking for something that is either unnecessary or
impossible (or both).

[Jos]

Hi Alex Dybenko, John Vinson,

I posted these questions previously on the MSDN Sql Server community and
therefore I asked myself if upsizing was a way to handle the basic question
as how to migrate from Access to Report Sql Server 2005...


Hi, Jonathan

There seems to be some confusion as to what I want to achieve. I started with:
"My boss wants me to convert an Access.mdb coding to Report Sql 2005."
I tried to convert the VBA functions to UDF's in the Sql database. This you
said was not the proper way.
So now I try to embed the function in de .rdl file of the Report Project in
the code block.
According to:


Code SnippetSQL Server 2005 Books Online

Report Properties (Code Tab, Report Designer) Send Feedback
See Also
Collapse AllExpand All Language Filter: AllLanguage Filter:
MultipleLanguage Filter: Visual BasicLanguage Filter: C#Language Filter:
C++Language Filter: J#Language Filter: JScript
Reporting Services F1 Help > Report Designer F1 Help >
Visual Basic(Declaration)
C#
C++
J#
JScript

Use the Code tab of the Report Properties dialog box to define custom code
to be used by expressions within the report.

Options
Custom code
Type the methods to be used within the report. Code must be written in
Microsoft Visual Basic.






This code block however is not designed for development, as is it just a
memo-field, without any VB editting features.

Another way appears to be using Custom Assemblies:


Code SnippetSQL Server 2005 Books Online

Using Custom Assemblies with Reports Send Feedback
See Also
Collapse AllExpand All Language Filter: AllLanguage Filter:
MultipleLanguage Filter: Visual BasicLanguage Filter: C#Language Filter:
C++Language Filter: J#Language Filter: JScript

Visual Basic(Declaration)
C#
C++
J#
JScript

In Reporting Services, you can write custom code for report item values,
styles, and formatting. For example, you can use custom code to format
currencies based on locale, flag certain values with special formatting, or
apply other business rules that are in practice for your company. One way to
include this code in your reports is to create a custom code assembly using
the Microsoft .NET Framework that you can reference from within your report
definition files. The server calls the functions in your custom assemblies
when a report is run. Custom assemblies can be used to retrieve specialized
functions that you plan to use in your reports.







As I mentioned, I do not know how to make a custom assembly, but I am
willing to learn.

So I do not particulary want to develop VBA scripts, I just want to find the
best way to convert them to SQL200r Report Services.....





Report Abuse



18 Aug 2008, 1:40 PM
BeforeAndAfter1974



Posts 95
Re: Database Independent Routines in Sql 2005
Was this post helpful ?
Successfully Unmarked post as an Answer !


Josje,



Custom assemblies are a bit extreme when converting from Access. It falls
back on an old philosophy, "just because we can, doesn't mean we should."



Access has an upsizing wizard tool that will convert the code into SQL for
you. Go To Tools | Database Utilities | Upsizing wizard. While this won't
convert 100% of the source perfectly, or it might, it will give you a solid
foundation to build on.



Edit: Considering Access is designed to be an all-encompassing evironment
for db and forms development, it will be difficult if not impossible to
replicate the environment into SQL Server without a front end interactive
form other than a Report.



A.D.T.




--------------------------------------------------------------------------------
Luck is when preparation meets opportunity.


Report Abuse



19 Aug 2008, 2:24 PM
Josje



Posts 22 Re: Database Independent Routines in Sql 2005



Hi, Jonathan, A.D.T.,

I have posted questions about Upsizing Wizard on the Access Forum.

Thanks for replying.....
--
Jos

Post by John W. Vinson

Post by Jos
My boss wants me to convert an Access.mdb coding to Report Sql 2005.

I'm pretty sure you will need to a) just leave the data in SQL/Server where it
is already; the upsizing wizard is for moving data from an Access JET database
into SQL, so it's not needed here; and b) scrap the entire Access database
frontend and recreate its functionality from scratch in SQL Reporting
Services. They are *very* different programming environments and there is, to
my knowledge, no way to convert an Access Report into a SQL Reporting Services
report. Your boss may be asking for something that is either unnecessary or
impossible (or both).
--
John W. Vinson [MVP]

[John W. Vinson]

On Thu, 21 Aug 2008 07:36:01 -0700, Jos <***@discussions.microsoft.com> wrote:


I'm sorry, but I couldn't find a specific question in these reposted
discussions.

I have never tried to convert VBA modules to T/SQL scripts, and do not know
how one would do so. I'm sorry I didn't make any useful contribution to the
thread.

Post by Jos
I posted these questions previously on the MSDN Sql Server community and
therefore I asked myself if upsizing was a way to handle the basic question
as how to migrate from Access to Report Sql Server 2005...
Hi, Jonathan
"My boss wants me to convert an Access.mdb coding to Report Sql 2005."
I tried to convert the VBA functions to UDF's in the Sql database. This you
said was not the proper way.
So now I try to embed the function in de .rdl file of the Report Project in
the code block.
Code SnippetSQL Server 2005 Books Online
Report Properties (Code Tab, Report Designer) Send Feedback
See Also
C++Language Filter: J#Language Filter: JScript
Reporting Services F1 Help > Report Designer F1 Help >
Visual Basic(Declaration)
C#
C++
J#
JScript
Use the Code tab of the Report Properties dialog box to define custom code
to be used by expressions within the report.
Options
Custom code
Type the methods to be used within the report. Code must be written in
Microsoft Visual Basic.
This code block however is not designed for development, as is it just a
memo-field, without any VB editting features.
Code SnippetSQL Server 2005 Books Online
Using Custom Assemblies with Reports Send Feedback
See Also
C++Language Filter: J#Language Filter: JScript
Visual Basic(Declaration)
C#
C++
J#
JScript
In Reporting Services, you can write custom code for report item values,
styles, and formatting. For example, you can use custom code to format
currencies based on locale, flag certain values with special formatting, or
apply other business rules that are in practice for your company. One way to
include this code in your reports is to create a custom code assembly using
the Microsoft .NET Framework that you can reference from within your report
definition files. The server calls the functions in your custom assemblies
when a report is run. Custom assemblies can be used to retrieve specialized
functions that you plan to use in your reports.
As I mentioned, I do not know how to make a custom assembly, but I am
willing to learn.
So I do not particulary want to develop VBA scripts, I just want to find the
best way to convert them to SQL200r Report Services.....
Report Abuse
18 Aug 2008, 1:40 PM
BeforeAndAfter1974
Posts 95
Re: Database Independent Routines in Sql 2005
Was this post helpful ?
Successfully Unmarked post as an Answer !
Josje,
Custom assemblies are a bit extreme when converting from Access. It falls
back on an old philosophy, "just because we can, doesn't mean we should."
Access has an upsizing wizard tool that will convert the code into SQL for
you. Go To Tools | Database Utilities | Upsizing wizard. While this won't
convert 100% of the source perfectly, or it might, it will give you a solid
foundation to build on.
Edit: Considering Access is designed to be an all-encompassing evironment
for db and forms development, it will be difficult if not impossible to
replicate the environment into SQL Server without a front end interactive
form other than a Report.
A.D.T.
--------------------------------------------------------------------------------
Luck is when preparation meets opportunity.
Report Abuse
19 Aug 2008, 2:24 PM
Josje
Posts 22 Re: Database Independent Routines in Sql 2005
Hi, Jonathan, A.D.T.,
I have posted questions about Upsizing Wizard on the Access Forum.
Thanks for replying.....

[Alex Dybenko]

Hi,
you can not directly convert VBA function to TSQL, but you can rewrite most
of them. Or you can make .net functions. Anyway - a lot of work, so I would
better stay with access
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Jos
Hi Alex Dybenko, John Vinson,
I posted these questions previously on the MSDN Sql Server community and
therefore I asked myself if upsizing was a way to handle the basic question
as how to migrate from Access to Report Sql Server 2005...
Hi, Jonathan
"My boss wants me to convert an Access.mdb coding to Report Sql 2005."
I tried to convert the VBA functions to UDF's in the Sql database. This you
said was not the proper way.
So now I try to embed the function in de .rdl file of the Report Project in
the code block.
Code SnippetSQL Server 2005 Books Online
Report Properties (Code Tab, Report Designer) Send Feedback
See Also
C++Language Filter: J#Language Filter: JScript
Reporting Services F1 Help > Report Designer F1 Help >
Visual Basic(Declaration)
C#
C++
J#
JScript
Use the Code tab of the Report Properties dialog box to define custom code
to be used by expressions within the report.
Options
Custom code
Type the methods to be used within the report. Code must be written in
Microsoft Visual Basic.
This code block however is not designed for development, as is it just a
memo-field, without any VB editting features.
Code SnippetSQL Server 2005 Books Online
Using Custom Assemblies with Reports Send Feedback
See Also
C++Language Filter: J#Language Filter: JScript
Visual Basic(Declaration)
C#
C++
J#
JScript
In Reporting Services, you can write custom code for report item values,
styles, and formatting. For example, you can use custom code to format
currencies based on locale, flag certain values with special formatting, or
apply other business rules that are in practice for your company. One way to
include this code in your reports is to create a custom code assembly using
the Microsoft .NET Framework that you can reference from within your report
definition files. The server calls the functions in your custom assemblies
when a report is run. Custom assemblies can be used to retrieve specialized
functions that you plan to use in your reports.
As I mentioned, I do not know how to make a custom assembly, but I am
willing to learn.
So I do not particulary want to develop VBA scripts, I just want to find the
best way to convert them to SQL200r Report Services.....
Report Abuse
18 Aug 2008, 1:40 PM
BeforeAndAfter1974
Posts 95
Re: Database Independent Routines in Sql 2005
Was this post helpful ?
Successfully Unmarked post as an Answer !
Josje,
Custom assemblies are a bit extreme when converting from Access. It falls
back on an old philosophy, "just because we can, doesn't mean we should."
Access has an upsizing wizard tool that will convert the code into SQL for
you. Go To Tools | Database Utilities | Upsizing wizard. While this won't
convert 100% of the source perfectly, or it might, it will give you a solid
foundation to build on.
Edit: Considering Access is designed to be an all-encompassing evironment
for db and forms development, it will be difficult if not impossible to
replicate the environment into SQL Server without a front end interactive
form other than a Report.
A.D.T.
--------------------------------------------------------------------------------
Luck is when preparation meets opportunity.
Report Abuse
19 Aug 2008, 2:24 PM
Josje
Posts 22 Re: Database Independent Routines in Sql 2005
Hi, Jonathan, A.D.T.,
I have posted questions about Upsizing Wizard on the Access Forum.
Thanks for replying.....
--
Jos

Post by John W. Vinson

Post by Jos
My boss wants me to convert an Access.mdb coding to Report Sql 2005.

I'm pretty sure you will need to a) just leave the data in SQL/Server where it
is already; the upsizing wizard is for moving data from an Access JET database
into SQL, so it's not needed here; and b) scrap the entire Access database
frontend and recreate its functionality from scratch in SQL Reporting
Services. They are *very* different programming environments and there is, to
my knowledge, no way to convert an Access Report into a SQL Reporting Services
report. Your boss may be asking for something that is either unnecessary or
impossible (or both).
--
John W. Vinson [MVP]

[Brendan Reynolds]

"Jos" <***@discussions.microsoft.com> wrote in message news:A2E5E67B-E033-411B-81B6-***@microsoft.com...

You can, as you've discovered, add code to the reports in SQL Server
Reporting Services, and it does use 'classic' Visual Basic, not .NET.
However, as you've also discovered, you don't have a full-featured VB
editing environment, just a big text box in which to type your code.
Another issue is that much of the code behind an Access report might need to
be re-written to work with SQL Server Reporting Services, because although
the core languages are the same, the objects and properties that the code is
manipulating are very different. Using custom assemblies you would have all
the features of Visual Studio available to you, but then you could not use
'classic' VB, you'd have to learn VB.NET or C# or another .NET language, and
familiarize yourself with the .NET Framework.

Converting from an Access application using a JET database to an Access
application using a SQL Server database is a non-trivial project. Converting
from Access reports to SQL Server Reporting Services is another and very
different non-trivial project. It sounds like you may be trying to do both.
That's going to take a lot of time, and require the acquisition of a lot of
new knowledge and new skills.

Good luck.