CODEWITHSUNDEEP
sql-serverms-access
rugbert
rugbert

Reputation: 12633

connecting to sql server from access

So Im in the process of connecting Access to SqlServer. What I need to do is:

So that way the only thing done on the client machine is using the forms in Access, and the queries, reports, and data are all dealt with on the server.

I guess my question is how to properly migrate these queries? I dont know how Im going to connect the queries to the Access forms to resolve these external variable errors like:

SELECT AcuteHospitals.HospitalName, Sum(IIf([Quarterly CLABSI Numbers by Hospital]![Number of CLABSI] Is Null,"--",[Quarterly CLABSI Numbers by Hospital]![Number of CLABSI])) AS CLABSI, Sum(IIf([Quarterly CLABSI Numbers by Hospital]![Central Line Days] Is Null,"--",IIf([Quarterly CLABSI Numbers by Hospital]![Central Line Days]=0,0,Format([Quarterly CLABSI Numbers by Hospital]![Central Line Days],"#,###")))) AS CLD, [Quarterly CLABSI Numbers by Hospital].SizeCat, [Quarterly CLABSI Numbers by Hospital].BedSize
FROM AcuteHospitals LEFT JOIN [Quarterly CLABSI Numbers by Hospital] ON AcuteHospitals.HospitalName = [Quarterly CLABSI Numbers by Hospital].HospitalName
WHERE ((([Quarterly CLABSI Numbers by Hospital].SummaryYQ) In ("2010Q1","2010Q2")))
GROUP BY AcuteHospitals.HospitalName, [Quarterly CLABSI Numbers by Hospital].SizeCat, [Quarterly CLABSI Numbers by Hospital].BedSize
ORDER BY AcuteHospitals.HospitalName;

gives me the following errors:

SSMA warning messages:
*   A2SS0030: View '2010Q1-Q2 CLABSI Rate by Hospitalstep1test' has a name that might cause problems for the Access application to function correctly against SQL Server.
*/


   /* 
   *   SSMA error messages:
   *   A2SS0069: External variable cannot be converted.

   *   A2SS0061: The identifier 'Format(UNKNOWN, VARCHAR)' was not converted.

I've fixed the Format error but I dont know how to resolve the other ones

Upvotes: 1

Views: 1225

Answers (3)

Aaron Kempf
Aaron Kempf

Reputation: 588

there is now support for FORMAT function in SQL 2012.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 48989

The recommended approach here is to upsize ONLY your data to SQL server.

You then simply link the Access "front end" to the SQL database.

You DO NOT want to create or use an access data project.

In fact as a general rule it's far better split your database first. And in fact if this application had been developed and maintained correctly over the years, it's pretty much a given that at 1. In time it was split into a front end or so called application part, and that of a backend which is the data only part.

It is the data only that you want to send up to SQL server, you do not want to convert your application to a data project, or do anything else which will result in you having to rewrite a your VBA code and that of having to rebuild your queries.

So you take the DATA ONLY part and upsize the data to SQL server. The result is usually the upsizing wizard will create linked tables for you. Do not create an access data project here, simply upsize your data.

At this point you can import those table links to from your back end into your front end, or simply delete your existing table links you have in your access front end, and now RE link those tables to SQL server .

At this point in time all of your current queries will continue to function, and all of your current queries will not be have to be rewritten.

There's no need whatsoever to recreate all of your queries. The only time you'll have to recreate some queries is perhaps for reasons of performance. That is something you can worry about and work on over time. You will also find that 99% of your VBA and even DAO recordset code will continue to run just fine.

If you upsize to an Access Data Project (ADP), then you have to re-write any DAO code in your VBA. And you MUST use ADO which as noted Microsoft has quite said it's much on its way out. Microsoft as stated that the future technology is ODBC and for Access that means DAO.

If you want to upsize your tables to the latest and greatest eddition of SQL server called Azure, the ONLY support baked into Access 2010 which as stated future road technology is using linked tables (ODBC). So in Access this means you are to continue using DAO.

If your application did use ADO, then I STILL recommend using linked tables to SQL server as opposed to an attempted conversion of Access database to a ADP project.

So, using linked tables means your existing queries will not have to be modified.

Upvotes: 3

Andomar
Andomar

Reputation: 238058

It's just saying it can't convert this to SQL Server dialect:

Format([Quarterly CLABSI Numbers by Hospital]![Central Line Days],"#,###"))))

Because T-SQL doesn't have a format function, this is at least defensible.

The way I last converted an Access database was:

  1. Importing the tables into SQL Server
  2. Create an empty .ADB file. Those files use SQL Server as their backend.
  3. Then, one by one, I import the forms and reports from the old MDB to the new ADB. Each one can take anything from small changes to a complete rewrite.

Upvotes: 1

Related Questions