Access 2003 ADP to SQL 2000

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

Guest

Dear ALL:

How do I get to sql 2000 from an Access 2003 database front end?
Nothing seems to work.

Also, am I correct that I have to change all queries against, and all other
references to, table names from my Access back end database, because the
names change from <tablename> to <dbo_tablename or dbo.tablename>?

(I have imported my Access db into my sql db.)
I have been provided with a sql server and have the server name and the db
name and it sees me with my userid and no password. I have an ODBC
connection set up (Metrics_SQL) and it tests ok.

With the adp's File|Connections... menu, I noticed that the dialog does not
provide me with any choice of an ODBC connection and just filling in my
server name and db name is not working, whether I use NT or specific user
security.

I am able to -link- to the sql db and tables, in Access 2000 using ODBC, but
I can't edit the table structures, even tho the db admin gave me complete
rights. Also, in Access, it appears, by definition, that you can't edit
-linked- tables.
 
Michael,
Every one of your porblems is solvable.
I do not know where you got so many mis-impressions.
I have used Access with SQL Server for over 8 years. They work very well
together.

1. A2003 gives you 2 different ways to talk to SQL Server:
Linked tables in an mdb file or direct connect in .adp file.
You should pick one approach, not both.
I have always (and MS now) recommend you use linked tables in an .mdb file.

2. When you link using the wizard, the tables are connected as
dbo.tablename.
This is a real PITA but can easily be solved b using code to link the tables
and omitting the dbo.
(See attached code sample below.)

3. I set up system DSNs and connect via ODBC. Works fine.
(You can also skip the DSN if you want to use DSNless connections.)

4. .adp files do not use ODBC. They use ADO. But you should not use them
since you will be in an .mdb.

5. Uneditable tables in Access when linked to SQL Server are due to lack of
Primary Key on the tables.
You should go add a PK to every table. Also recommended is adding a
timestamp field to every table. (This is a special data type that really
helps Access with concurrency when editing data. Access does not have to
comapre every field if a timestamp is available. Makes updates safer and
more secure.

Sample code to attach:

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
 
Thank you Joe. Printed and am studying.

Hmm. I thought the ADP projects were specially designed for sql and might
be faster and more stable. Oh, well. Will use your method tho.

I do notice that my link mgr says the tables are dbo_ (not dbo.), but you
may mean more internally, where the code needs to see it.

Also, my tables are keyed. I thought, by definition, Access couldn't edit
"linked" tables?
My data was imported to a test sql server by my corporate IT dept, who
manages the servers. Not all the fields came in correctly, rather
in-correctly.

I need to change structure, mostly datatypes and sizes right now. Brought
all my number integer key fields in as currency, for example.

I do not have sql client installed and have no other tool for managing my
database (yet). I am now ready to install vs.net enterprise architect, with
its sql server local db and management tools. I hope that helps with the
test sql server situation.

Thanks, again.

MichaelM
Joe Fallon said:
Michael,
Every one of your porblems is solvable.
I do not know where you got so many mis-impressions.
I have used Access with SQL Server for over 8 years. They work very well
together.

1. A2003 gives you 2 different ways to talk to SQL Server:
Linked tables in an mdb file or direct connect in .adp file.
You should pick one approach, not both.
I have always (and MS now) recommend you use linked tables in an .mdb file.

2. When you link using the wizard, the tables are connected as
dbo.tablename.
This is a real PITA but can easily be solved b using code to link the tables
and omitting the dbo.
(See attached code sample below.)

3. I set up system DSNs and connect via ODBC. Works fine.
(You can also skip the DSN if you want to use DSNless connections.)

4. .adp files do not use ODBC. They use ADO. But you should not use them
since you will be in an .mdb.

5. Uneditable tables in Access when linked to SQL Server are due to lack of
Primary Key on the tables.
You should go add a PK to every table. Also recommended is adding a
timestamp field to every table. (This is a special data type that really
helps Access with concurrency when editing data. Access does not have to
comapre every field if a timestamp is available. Makes updates safer and
more secure.

Sample code to attach:

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

--
Joe Fallon
Access MVP



Michael Miller said:
Dear ALL:

How do I get to sql 2000 from an Access 2003 database front end?
Nothing seems to work.

Also, am I correct that I have to change all queries against, and all
other
references to, table names from my Access back end database, because the
names change from <tablename> to <dbo_tablename or dbo.tablename>?

(I have imported my Access db into my sql db.)
I have been provided with a sql server and have the server name and the db
name and it sees me with my userid and no password. I have an ODBC
connection set up (Metrics_SQL) and it tests ok.

With the adp's File|Connections... menu, I noticed that the dialog does
not
provide me with any choice of an ODBC connection and just filling in my
server name and db name is not working, whether I use NT or specific user
security.

I am able to -link- to the sql db and tables, in Access 2000 using ODBC,
but
I can't edit the table structures, even tho the db admin gave me complete
rights. Also, in Access, it appears, by definition, that you can't edit
-linked- tables.
Click to expand...
Click to expand...
 
Hi Joe,

I tried the code with two bad results:
1) The code stopped at half the tables in my local tabel and said that
tabledefs was no longer set.

2) Now, with the connections that I did get, my Access queries now read:
Expr1:<tablename1>, Expr2:<tablename2>
thereby negating all my fieldnames which will confuse my forms and results.

How do I get rid of the Expression in a query, which is based on the table
that I just linked to. My tables -did- come in without the "dbo_" prefix,
fine.

mdm
 
Hi Joe,

I just noticed another problem.
All my Access queries on those tables -have- altered their fieldname lines
to Expr1: <fieldname>
and also

all the table (query) joins are gone. None of the tables are joined anymore.

even if I correct these queries (a lot of work), won't they come back again,
the next time I relink?

mdm


Joe Fallon said:
Michael,
Every one of your porblems is solvable.
I do not know where you got so many mis-impressions.
I have used Access with SQL Server for over 8 years. They work very well
together.

1. A2003 gives you 2 different ways to talk to SQL Server:
Linked tables in an mdb file or direct connect in .adp file.
You should pick one approach, not both.
I have always (and MS now) recommend you use linked tables in an .mdb file.

2. When you link using the wizard, the tables are connected as
dbo.tablename.
This is a real PITA but can easily be solved b using code to link the tables
and omitting the dbo.
(See attached code sample below.)

3. I set up system DSNs and connect via ODBC. Works fine.
(You can also skip the DSN if you want to use DSNless connections.)

4. .adp files do not use ODBC. They use ADO. But you should not use them
since you will be in an .mdb.

5. Uneditable tables in Access when linked to SQL Server are due to lack of
Primary Key on the tables.
You should go add a PK to every table. Also recommended is adding a
timestamp field to every table. (This is a special data type that really
helps Access with concurrency when editing data. Access does not have to
comapre every field if a timestamp is available. Makes updates safer and
more secure.

Sample code to attach:

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

--
Joe Fallon
Access MVP



Michael Miller said:
Dear ALL:

How do I get to sql 2000 from an Access 2003 database front end?
Nothing seems to work.

Also, am I correct that I have to change all queries against, and all
other
references to, table names from my Access back end database, because the
names change from <tablename> to <dbo_tablename or dbo.tablename>?

(I have imported my Access db into my sql db.)
I have been provided with a sql server and have the server name and the db
name and it sees me with my userid and no password. I have an ODBC
connection set up (Metrics_SQL) and it tests ok.

With the adp's File|Connections... menu, I noticed that the dialog does
not
provide me with any choice of an ODBC connection and just filling in my
server name and db name is not working, whether I use NT or specific user
security.

I am able to -link- to the sql db and tables, in Access 2000 using ODBC,
but
I can't edit the table structures, even tho the db admin gave me complete
rights. Also, in Access, it appears, by definition, that you can't edit
-linked- tables.
Click to expand...
Click to expand...
 
Mike,
I have used this exact code reliably for over 6 years.
So you have a different problem.

1. I would test the code on a new .mdb file not a Production one.

2. Make a backup.

3. Test it for a small number of tables.
See if it works as it should.

4. Then add lots more tables and test it again.

If it breaks then you know there is a problem with one of the tables.
Perhaps your account does not have permission to use it.

Get it working completely in a test system.
Do it by taking small steps.

Once it works, then try it on a copy of a production .mdb.
Should be fine at that point.

Not sure what you did to the queries.
They should not have been affected by this.
 
Thanks Joe. Wasn't sure if you reviewed old messages.

I did the whole thing from scratch again, starting with a new .mdb

I did the table import first (so the queries could see the tables - although
I did not open a query till the whole import was done before, but tables were
last)

This time it did work and the table references in the queries held up.

Now, my form is taking 5 minutes to load with various other errors that I
will track down. Can I assume that a query should go faster with the sql
links, than they did with my split .mdb (and a slow file server)?

The most notable thing happening now, is with I Left Outer Join 3 tables in
Access Query Design, it tells me that the joins were ambigious. All the
queries translated into INNER JOINS only. That may not show all my data in
table1, where the user has not filled out keys for table2 or table3 yet.

At least I have stuff to look at now. Thanks
If you have any ideas on the above, I'd love to hear the best way to do my
queries.
 
Mike,
Glad to hear it still works. <g>
=======================================================
"Now, my form is taking 5 minutes to load with various other errors that I
will track down. Can I assume that a query should go faster with the sql
links, than they did with my split .mdb (and a slow file server)?"

Nope. Bad assumption. Local .mdb files are *faster* than linked SQL Server
tables.
(A bit counterintuitive and not true in all cases but...)

There are whole books on optimizing Access and SQl Server.
I recommend Mary Chipman's.

The idea is that a query of a single table should be very fast.
However, when you JOIN linked tables you could be in for some slowdown due
to the way Jet gets involved.
The idea is to bypass JET in those cases and send the query to SQL Server as
a SPT query.
(SQL Pass Through Query.)
SPT queries are written in the dialect of the SERVER - NOT Access.
So for SQL Server you write T-SQL.

SPT queries return Read Only Results. So they are fine for picklists and
other display forms.
An Edit form should really only be for a single table (or View) so a regular
Access query should be fine.

The other "big idea" is to always set up some criteria before you ever open
a form.
Then return the minimum number of records you can.
(A user can only edit one of them at a time anyway...)
Never just bind a form to a table/query. (Common practice in Access with
local tables. Bad idea over a network.)



=======================================================
"The most notable thing happening now, is with I Left Outer Join 3 tables in
Access Query Design, it tells me that the joins were ambigious. All the
queries translated into INNER JOINS only. That may not show all my data in
table1, where the user has not filled out keys for table2 or table3 yet."

See above about SPT queries. Use T-SQL and let the Server handle it.
=======================================================
 
Back
Top