access .mdb to SQL ODBC

[varean]

Access tables were migrated to SQL via the upsizing wizard, connecting to tables via an ODBC connection.
Need to know how to change the connect string to now connect to the ODBC tables.
here's the code when the tables were linked to MS access:

'Option Explicit
Sub localnet()
Set ws = DBEngine(0)
Set db = ws.OpenDatabase("c:\work\satp\usersinfo.mdb")
End Sub
--------------------------------------------------------
Sub servernet()
Set ws = DBEngine(0)
Set db = ws.OpenDatabase("\\hpcsrv\access\satp1.mdb")
End Sub
---------------------------------------------------------
Sub curDB()
'Set db = CurrentDb
Set ws = DBEngine(0)
Set db = ws.OpenDatabase("\\hpcsrv\access\satp1.mdb")
End Sub

 

[SkipVought]

http://www.carlprothman.net/Default.aspx?tabid=81

http://www.connectionstrings.com/

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[varean]

Thanks for you reply. I found this code,I will try it and see if it works.

Dim db As Database
Dim ws As WorkSpace
Dim rs As Recordset
Dim connectstring As String
connectstring = "ODBC;DSN=<datasource name>;UID=<username>;_
PWD=<password>;DATABASE=PUBS"

' NOTE: In the "connectstring" line above replace <datasource
' name> with the name of your data source for SQL Server; replace
' <username> with the username used to log on to the data
' source; and replace <password> with the appropriate password.

On Error GoTo Trans_Work_Err
Set ws = dbengine.Workspaces(0)
Set db = ws.OpenDatabase("", False, False, connectstring)
Set rs = db.OpenRecordset("dbo.authors") 'Opens the recordset.

 

[varean]

got it to work but now I'm getting an error message on this one.......Remember the database is connected via ODBC.
it fails at the rst.Index = "socialsecurity" - Is there a way I can code this differently. I heard that INDEX and SEEK does not do well with ODBC connections...... thanks!

Option Compare Database
Sub ssnsearch()
Call curDB
Set rst = CurrentDb.OpenRecordset("client demographic")
rst.Index = "socialsecurity"
rst.Seek "=", Forms![Services]!ssn
If Not rst.NoMatch Then
Forms!Services!AdmissionDate = rst!AdmissionDate
Forms!Services!grp = rst!ServiceRequested
Forms!Services!CustomerID = rst!CustomerID
Forms!Services!DischargeDate = rst!DischargeDate
Else
MsgBox "Client Not on Record. Try Again."
Forms!Services!ssn = 0
Forms!Services!payorinfo.SetFocus
Forms!Services!ssn.SetFocus
End If
DoCmd.Close acForm, "dnose"
End Sub

 

[JerryKlmns]

I would change that, to accept ssn as a parameter for the "client demographic" query which runs on server. For this to happen : "Client Not on Record. Try Again.", both rst.BOF and rst.EOF are true. So check that and go on

 

[varean]

JerryKlmns,
"Client demographics" is an ODBC connected table. The problem is when the code gets to rst.Index = "socialsecurity" . I believe there's an issue with linked tables and indexes.
I would love for someone to tell me what would work, actually modify what I have so that I can try it. I'm not a programmer so it's difficult to comprehend some of the replies........... thanks!

 

[JerryKlmns]

varean

If you are looking for a specific record it is most recommended to open a query including a WHERE clause and not open the table/query and go find that value.

So

Dim strSQL As String
strSQL = 
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [client demographic] WHERE socialsecurity=" &  Forms![Services]!ssn )
If rst.BOF AND rst.EOF Then
   MsgBox "Client Not on Record. Try Again."
   Forms!Services!ssn = 0
   Forms!Services!payorinfo.SetFocus
   Forms!Services!ssn.SetFocus
Else
   Forms!Services!AdmissionDate = rst!AdmissionDate
   Forms!Services!grp = rst!ServiceRequested
   Forms!Services!CustomerID = rst!CustomerID
   Forms!Services!DischargeDate = rst!DischargeDate
End If

What I recommend is to make the SQL statement used for the recordset to be a stored procedure in SQL Server faq705-2531

 

[varean]

Jerryklymns,
thanks so much for your time and input, this looks good.