My turn for access to sql questions :p

[TheSingingCat]

Good day,

I am evaluating sql server 2000 and debating moving our companies main
database over. I see though that connecting to an SQL server via access and
odbc changes a few things as far as code goes.

Oh, also this access db was originally done in Access97' using DAO 3.5

What I have done so far is imported all our data to the sql server and now
basically reworking a front end client on Access 2002. I was trying to do a
basic little update to the SQL database but of course much of the coding in
dao 3.5 doesn't work with sql it would seem. Sadly, most of my experience
with access has been on 97 and dao35.

This is what I tried to do:
--------------------
Dim DB As Database
Dim RS As Recordset
Dim X, Y As Long

Set DB = DBEngine.Workspaces(0).Databases(0)
Set RS = DB.OpenRecordset("dbo_table1", dbOpenDynaset) 'this dbo_table1 is
on sqlserver now

X = Me![start]
Y = Me![stop]

Do While X <= Y
RS.AddNew
RS![OrderNum] = X
RS.Update
X = X + 1
Loop
--------------------
So as you can probably see I am grasping at straws to update the table on
the backend. While the odbc connection works out fine as I can link the
tables into my access front end, I lack the knowledge to reference the
tables on the back end.

Could anyone possibly point out how this could should be altered to tie in
with SQL? Secondly, while I consider my Access knowledge intermediate, I
obviously need some training on working with access 2002 and sql server,
could anyone suggestion a good book to at least get my feet wet?

Thank you.

 

[TheSingingCat]

Good day,

I am evaluating sql server 2000 and debating moving our companies main
database over. I see though that connecting to an SQL server via access and
odbc changes a few things as far as code goes.

Good lord have I been out of the loop by just working in Access 97.

- adp files
- mdb linked tables to sql data source
- odbc dsn or dsnless connections
- ado

Wow, so many different ways to work with access and sql, whichever is the
most efficient? I have to get to the bookstore this week.

 

[Tony Toews]

I am evaluating sql server 2000 and debating moving our companies main
database over. I see though that connecting to an SQL server via access and
odbc changes a few things as far as code goes.

Oh, also this access db was originally done in Access97' using DAO 3.5

What I have done so far is imported all our data to the sql server and now
basically reworking a front end client on Access 2002. I was trying to do a
basic little update to the SQL database but of course much of the coding in
dao 3.5 doesn't work with sql it would seem.

Sure it does. Hmm, I never had to worry about the dbo_ part. Ah,
yes, that's because my linking logic linked to the table name
including the dbo_ but then table name within Access had the dbo_
portion removed.

Could anyone possibly point out how this could should be altered to tie in
with SQL? Secondly, while I consider my Access knowledge intermediate, I
obviously need some training on working with access 2002 and sql server,
could anyone suggestion a good book to at least get my feet wet?

You need to update the OpenRecordset line slightly. But if the table
is linked then no massive changes are required. See the "You must
use the dbSeeChanges option with OpenRecordset when accessing a SQL
Server table that has an IDENTITY column" section at the "My random
thoughts on SQL Server Upsizing from Microsoft Access" page at my
website.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm