[X]My Assistant

[tuufbiz]

I have A2003 and SQL server 2016 express
I recently used SSMA to migrate a MDB database over to SQL server.
I have a command button that no longer works since it involves a DAO recordset based off of currentdb

CODE

Set db = CurrentDb
Set rs = db.OpenRecordset("tbltravel", dbOpenTable)

Can you use DAO with SQL server when Access is the FE?
Any suggestions on how I would change this to get it to work?


Thanks
This post has been edited by tuufbiz: Apr 15 2018, 09:04 PM

[GroverParkGeorge]

Yes, you can definitely use DAO with linked SQL Server tables.

Try "Set rs = db.OpenRecordset("SELECT * FROM tbltravel", dbOpenDynaset)

[GroverParkGeorge]

You may also need to use the dbSeeChanges Option.

Set rst =Db.OpenRecordset(NAME:="Select * FROM tbltravel", Options:=dbOpenDynaset + dbSeeChanges)

[tuufbiz]

Awesome....it works perfectly!
Thank you George

[tuufbiz]

Sorry to bother again but I'm getting a different error on another command button attached to a DAO recordset.

CODE

Private Sub cmdAlert_Click()
Dim confn As String
Dim list As Integer
Dim strsql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
list = Me.lstConfList.ListIndex + 1
confn = Me.lstConfList.Column(0, list)


Set db = CurrentDb
'Set rs = db.OpenRecordset("tbltravel", dbOpenTable)
Set rs = db.OpenRecordset(Name:="Select * FROM tbltravel", Options:=dbOpenDynaset + dbSeeChanges)

rs.MoveFirst
Do Until rs.EOF
    If rs.Fields("confnum").Value = confn Then
        If Len(rs.Fields("alert").Value & "") = 0 Then
            rs.Edit
            rs.Fields("alert").Value = "**"
            rs.Update
        Else
            rs.Edit
            rs.Fields("alert").Value = Null
            rs.Update
        End If
    End If
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

The code stops at "rs.Edit" (the line below "If Len(rs.Fields("alert").Value & "") = 0 Then"
I've attached the error message
I tried changing the the recordset code to: Set rs = db.OpenRecordset(Name:="Select * FROM tbltravel", Options:=dbOpenDynaset + dbSeeChanges) but it still doesn't work

Attached File(s)

 Capture3.JPG ( 19.9K )Number of downloads: 1

 

[GroverParkGeorge]

There is more than one possible reason for a linked table to be read-only.

But first, let's confirm that it truly is read-only, and not just the recordset.

Can you open it directly from the navigation pane and edit it that way?

If not, it is most likely because you didn't identify a primary key for the table when linking it. Is that done?

[tuufbiz]

I cannot edit the table directly and it does not have a primary key.
Can I easily add one at this point? If necessary I could delete the entire SQL database and start over. It is a very small database and this is my first experience with SQL server.

[GroverParkGeorge]

This is a common problem when first linking SQL Server tables. Sometimes Access does not recognize the Primary Key in such tables, and you are asked to identify one when linking. Also, Access can't update tables unless they DO have a Primary Key defined in SQL Server.

So that's the first step. Use SSMS to make sure this--and all other tables--have a Primary Key. When that's done, you'll need to delete and relink all of those tables in Access ( not in SQL Server, of course) which are based on those tables. When doing the relinking, pay close attention to any dialog asking you to identify the Primary Key(s) as the tables are linked. That's the only way those tables will be updateable in Access.

[GroverParkGeorge]

Here's an excellent introduction to the topic of using Access with SQL Server.

[GroverParkGeorge]

One more consideration. SQL Server offers a datatype called "Timestamp", or "RowVersion" which is very useful in helping Access know when it is possible/necessary to update a record in a table. I believe Armen's PPT on Access/SQL Server talks about this.

[tuufbiz]

Thanks George.

I know this is somewhat off topic but for an entire day I've been trying to install SQL server on my home PC. I have 2017 installed on my laptop running win10 and there are no issues.
My home PC is running win7pro sp1 64bit.
I've tried installing 2008 64bit, 2012 32bit&64bit, 2014 32bit&64bit. ( 2008 32bit won't even install)
The above install fine but when I try to connect to the server I always get the same error. Any ideas? Is this a common issue?
I've attached a screenshot of the error

Attached File(s)

 Capture1.JPG ( 33.87K )Number of downloads: 4

 

[GroverParkGeorge]

"...2008 64bit, 2012 32bit&64bit, 2014 32bit&64bit. "

So now you have at least four or five instances of SQL Server on the same computer? That's going to raise problems, I think.

Configuration to use any one of them is going to be the key. Do you need all of those versions? Or could you simplify by uninstalling all but the 2014 64 bit version?

That is, in fact, what I would recommend at the introductory level.

[tuufbiz]

Each time I installed a version I uninstalled the previous version.
Currently, I only have 32bit 2012 installed. I was just uninstalling and installing and trying to experiment as to what was causing the problem.
Since the program installs but I can't connect --- I also thought of changing the security from Windows authentication to SQL where an ID and PW are needed. Then I could at least log into the instance that was created on my laptop.
Currently I can't get past the initial Windows authentication screen where you connect to the server.

[GroverParkGeorge]

Okay, that makes sense (although I'd probably opt for the 64 bit version in contrast to Office, where the 32 bit version is recommended).

I also should have asked, but didn't, Since you are installing multiple versions, I has assumed you're working with the free version of SQL Server, SQL Server Express. Correct?

It installs what is known as a named instance and connecting to it requires both the server name and that instance name. For example, compare these choices.

 SQLServers.jpg ( 68.12K )Number of downloads: 0


I have to work with a number of local, remotely hosted and Azure servers. Note in this image some are full licensed versions, some are SQL Express versions. The SQL Express versions have names like SQLDATAMCHINE20\SQLEXPRESS,1433

See the part in Red? That's the syntax for referring to a named instance, i.e. the generic SQLExpress, on the server named SQLDataMchine20. (And yes, I fumbled fingered that when I set it up and no, I'm too lazy to correct it).

Also, note that I'm telling SSMS to connect via port 1433 on this SQL Express instance. I've often had to do that when there's more than one instance on a server.

The other part of your question has to do with the network. If you have a WIndows domain, with multiple computers joined to it, Windows Authentication works. However, most of us do NOT have a domain on a home network. We're more likely to have a WorkGroup. If that's the case, you MUST use SQL Server Authentication except when logging on from the same compute where the SQL Server is directly installed. I tend to use SQL Auth all the time out of habit although theoretically I can, and have, connected using WIndows Auth on the computer where the server is installed.

[tuufbiz]

Thanks George.
I figured out the problem ---- I was just installing the SQL management studio and was assuming that it was installing everything I needed. Once I installed the SQLEXPR_x86_ENU.exe file then everything worked.
In regards to your statement: "SQL Express versions have names like SQLDATAMCHINE20\SQLEXPRESS,1433"
-I setup SQL server 2017 express on my laptop running win10 64bit and used SSMA to migrate a MDB file over to it (so access is fe and SQL server is be --- we'll call the database "test1")
-I also setup SQL server 2014 express on my desktop PC running win7pro 64bit. I want to use this desktop and access as fe for the database "test1" that I setup on my laptop
-do I need to setup anything special on the "test1" database so it is "listening" for requests? Do I need to setup anything special on the desktop PC that is trying to access the "test1" database?

[tuufbiz]

I forgot to mention --- the laptop and desktop are on completely different networks at different locations

[GroverParkGeorge]

"Once I installed the SQLEXPR_x86_ENU.exe file then everything worked." That'll do it.

If you have two instances of SQL Server, one on your laptop and one on your desktop, they'll have different names.

One should be "\\MyLaptop\SQLServerExpressInstance"

And the other should be "\\MyDesktop\SQLServerExpressInstance"

Or the equivalent names you chose.

When you connect to one of them, you'll also tell Access with database to use.

The database itself doesn't listen, the SERVER listens.

You're now ready to configure the connection strings for Access, which is how you identify the Server and the database on that server, and then the table within that database.

Something like: ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=\\MyLaptop\SQLServerExpressInstance,1433;DATABASE=Test1;UID=YourSQLUserNameGoesHere;PW
D=YourSQLPasswordGoesHere;