Access/jet to SQL Server

[Ian Baker]

We have a client who has an urgent need for one of our "packaged" apps to be
used with full SQL Server (not just MSDE) and not Jet and as we know nothing
in this area we have some questions and any help is appreciated:
1. Our app uses Access Security with an mdw file for development which is
not distributed as the app is accessed by the user's own system.mdw file as
we have a couple of tables blocked (i.e. hold registration info etc) and
only accessed by RWOP queries in the FE. Can this still be done with SQL?
2. Some of our tables start with "Usys" and it seems the wizard will not
acknowledge them. What is the best way to address this?
3. Would we have to have different FE's i.e. one for a jet back end and one
for a SQL back end or can one FE be used with both and if so how could we do
it?
4. For us to start reading up on all of this would take 6 months and we
don't have that luxury so is there any "summary" documents or sites that
would help us?
5. Any other tips, suggestions, help etc that you could advise that we
should be mindful of?

Thanks for any advice you may be able to give

Regards
Ian Baker

 

[david epsom dot com dot au]

0) I would not accept this project as 'urgent' if you have
never done it before.

1) RWOP doesn't work with SQL tables. You will have to
rethink your security model and copy protection.

2) Rename the Usys tables. You can rename the table links as
Usys... and connect them to the new SQL Server tables.

3) You can use on FE for both Jet and SQL Server BE: you
can attach linked tables to either Jet or SQL Server.

4) If you have never done this before, at least look at
the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/

5) If you add data to subforms you will have to modify
your data entry process. If you use complex transactions
they won't work. If you use A97 you will have trouble
working out how to connect to SS2000.

(david)

 

[Joe Fallon]

I agree.
Don't take this on unless you have time to do it right.
It sounds like a disaster wiating to happen right now.

I suggest you hire someone with SQL Server experience to do the conversion
and design correctly.
(Not me.)

 

[Ian Baker]

Thanks Guys
Ok, I must be missing something huge here as I just installed SQL Server
(Dev), opened my BE using my Developer.mdw, upsized all tables to SQL Server
(using A2003 although the app is released in A2k & A2k2) accepting all the
defaults - everything ok. Open my FE, deleted all the jet table links,
created new table links using ODBC to the SQL Server db, renamed the table
links removing the dbo_ from each and tested and all seems ok - no problems
yet.

The security that I have is really to stop basic users from "playing" with a
registration table but I have no problems with an administrator having
access to the tables. The only I have now is:
1. What am I missing???????
2. How do I include & package the SQL backend (I am using the PDW) - perhaps
this is where the 6 months of reading will come into it.
3. I have to figure how to allow the user that when starting the FE to
select either "link to mdb" or "link to SQL Server" and the code to handle
the linking process.

Thanks again!

 

[david epsom dot com dot au]

1. What am I missing???????

The system is designed for bog standard applications (apart
from data problems and the autonumber problem with subforms).
If you have a bog standard application you may have escaped
with no problems at all.

2. How do I include & package the SQL backend

Any client that is using SQL Server will have a DBA that
will want to be part of the process. You can distribute
a SQL Server database as a .dbf file, and the DBA can
load it into SQL Server and set up the user permissions.

3. I have to figure how to allow the user that when starting the FE
select either "link to mdb" or "link to SQL Server" and the code to

A radio button on your re-link form....... Look at the connect
properties of one of your upsized linked tables to see what the
new connect property should look like.

(david)

 

[Ian Baker]

Hi David
"bog standard applications" - ?
The application is called Jackaroo IT with a 30 day trial available for
download at http://jackaroo.net.au and contains 60 tables, 300 queries (both
hard and SQL code), 60 forms (inc 22 subs), 18,000 lines of code and 2
ActiveX controls. We have clients using it with 26 concurrent users using
Jet but the new client wants to put a "light version" on all employee
desktops so the old Jet just won't take it.

 

[david epsom dot com dot au]

"bog standard applications" - ?

It not the size of the application that matters so much
as the coding practice. Although with an application
that size you are less likely to have some of the variety
that you might get within a bigger application.

(david)

 

[Joe Fallon]

SQL Server database files are .mdf not .dbf. One way to distribute it is to
doa backup and restore.
The best way is to write scripts to build all objects. (EM can do some of
this work, but its output should be reviewed and edited.)

---------------------------------------------------------------------------
See http://www.mvps.org/access/tables/tbl0009.htm
for code to re-link Jet tables.
---------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub