Migration to SQL Backend

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an access database that has considerable amount of coding behind it.
i use the DAO 3.6 object class and none of my code seems to be working once i
migrate a database to sql server. so my questions are:

1) is this normal?
2) does DAO not work with sql backends?
3) how should i proceed? as i don't wish to recode everything?

thanks in advance.
 
well the upsizing wizard created an adp file with the new links to the sql
server. i am still using the front end of the access database.
 
Sorry, Matthew, but we need to establish this without ambiguity, because the
correct answer depends on it - is the Access application in which you are
experiencing the problems with DAO code an ADP or an MDB?

If it is an ADP, then it is normal that existing DAO code will need to be
re-written to use ADO instead. If it is an MDB using ODBC-linked tables,
then the existing DAO code should require only minor modification.
 
So overall is ADO preferred to DAO? I seem to recall reading something,
somewhere that MS was actually discouraging the further use of DAO and
that it might in a future version of Access be formally deprecated prior
to removal.

David H
 
Well, once again I'm forced to answer a question with another question:
Preferred by who? There are certainly people who prefer ADO to DAO. But I am
not one of them.

DAO will be around at least as long as ADO. And as ADO has been replaced by
ADO.NET, which has little more in common with ADO than three letters in it's
name, the old argument that ADO was 'the future of data access technology'
while DAO was 'obsolete' no longer carries any weight at all.

But this has little to do with the subject of this thread.
 
Ok so pretty much you're saying - It's doesn't frack-ing* matter? Where
then can I find information on the pros and cons of each?

David H
*Watch Battlestar Galactica tonight on SciFi. Call your local cable
company for a subscription.
(PS no, I don't work for SciFi, NBC-Universal - I just enjoy the show.
You might be surprised that its more of an 'ER'-drama as opposed to Sci-Fi.)
 
Ok, but, what changes do i need to make to get my odbc connection to use DAO.
as i have no desire to learn ado, and i have over 10,000 lines of code in
this database already. i can't get one databse completed before the
technology changes..lol.
 
You still haven't answered the question, Matthew, but as you mention ODBC,
I'm going to assume this is an MDB we're talking about. What problems are
you experiencing? Is there an error message?
 
I have chosen to keep an MDB file and use an odbc connection to the sql
server, as i use a lot of temporary tables in my calculations which must
remain local to the app. the conversion goes slow, but well. most forms
without code will work just fine. only forms that use the database object
and recordset objects fail to load because of what seems to be object
declaration issues.
 
AHHHAAA.

Try this.

Remove the reference to the ADO Library (if it is checked).
Make sure you have a reference to the DAO Library.

Compile. If you get not errors, try running your database.

THe problem is that DAO and ADO both have objects with the same name and if you
don't specify the library as in

DIM DAO.Recordset

you often get the other library object. Problem is the properties of the two
objects are different
 
David - over the last few months, MS has openly stated that an .mdb with
linked tables and DAO code is the preferred solution to using ADO and .adp
files. (Glad I never switched!)

Matthew - Once the tables are in SQL Server, make a copy of your .mdb file,
delete all the local Access tables and link to SQL Server tables. If you
link manually each table name has a dbo_ prefix which you need to manually
remove so that the linked table name is identical to the old Jet table name.
At this point all your queries and reports should still work. Get a copy of
Mary Chipman's book to see how to optimize the DB further. But at least it
should be working without any changes.


FYI - I use code like this to link to SQL Server DBs. It has many useful
things in it. One of the best is that the dbo_ problem goes away and I can
re-link to Test DBs, Production DBs, etc and not lose a beat. Also, I
*always* delete all old links and re-create them. I have had too many
glitches just trying to refresh them.
-----------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(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
 
Just wanted to clearly separte the threads.
I have chosen to keep an MDB file and use an odbc connection to the sql
server, as i use a lot of temporary tables in my calculations which must
remain local to the app. the conversion goes slow, but well. most forms
without code will work just fine. only forms that use the database object
and recordset objects fail to load because of what seems to be object
declaration issues.



:
Click to expand...
 
Just trying to keep the two threads separte

Joe said:
David - over the last few months, MS has openly stated that an .mdb with
linked tables and DAO code is the preferred solution to using ADO and .adp
files. (Glad I never switched!)

Matthew - Once the tables are in SQL Server, make a copy of your .mdb file,
delete all the local Access tables and link to SQL Server tables. If you
link manually each table name has a dbo_ prefix which you need to manually
remove so that the linked table name is identical to the old Jet table name.
At this point all your queries and reports should still work. Get a copy of
Mary Chipman's book to see how to optimize the DB further. But at least it
should be working without any changes.


FYI - I use code like this to link to SQL Server DBs. It has many useful
things in it. One of the best is that the dbo_ problem goes away and I can
re-link to Test DBs, Production DBs, etc and not lose a beat. Also, I
*always* delete all old links and re-create them. I have had too many
glitches just trying to refresh them.
-----------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(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
Click to expand...
 
So then what was the point of ADO to begin with?

Joe said:
David - over the last few months, MS has openly stated that an .mdb with
linked tables and DAO code is the preferred solution to using ADO and .adp
files. (Glad I never switched!)

Matthew - Once the tables are in SQL Server, make a copy of your .mdb file,
delete all the local Access tables and link to SQL Server tables. If you
link manually each table name has a dbo_ prefix which you need to manually
remove so that the linked table name is identical to the old Jet table name.
At this point all your queries and reports should still work. Get a copy of
Mary Chipman's book to see how to optimize the DB further. But at least it
should be working without any changes.


FYI - I use code like this to link to SQL Server DBs. It has many useful
things in it. One of the best is that the dbo_ problem goes away and I can
re-link to Test DBs, Production DBs, etc and not lose a beat. Also, I
*always* delete all old links and re-create them. I have had too many
glitches just trying to refresh them.
-----------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(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
Click to expand...
 
So then what was the point of ADO to begin with?
Click to expand...

"Nothing is sadder than to see a beautiful idea brutally done to death
by a vicious gang of facts".

ADO was apparently a good theory, an engine-independent database
communication tool. In practice it simply did not work out.

John W. Vinson[MVP]
 
Back
Top