Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

MSAccess and SQL server

I have a database in sql server , and I have MSAccess as a client. I would like to know how to programmatically, retrieve through a query some records located on table1 & table2 & table3 that are related by keys. this query will be the recordset, and then go from the top of the recordset and skip down looking up for a string "BARB" in the field3 of the recordset.


sql server database name : Mydb
Table1   related to table2 on employeecode field
table2    related to table3 on functioncode    field



thanks




0
Chuckbuchan
Asked:
Chuckbuchan
  • 4
  • 3
1 Solution
 
Arthur_WoodCommented:
what version of Access are you using.  Are the Tables in SQL Server linked to your Access front end?

AW
0
 
ChuckbuchanAuthor Commented:
I am using MSAccess 2000 and the tables are on the SQL server.

thaks
0
 
Arthur_WoodCommented:
are the tables LINKED in your Access database (do you have LINKS to the SQL Server back-end?)

AW
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Arthur_WoodCommented:
Dim rs as Recordset
Dim cn as Connection
Dim strSQL as string

set cn = New Connection

cn.ConnectionString ="Provider=sqloledb;" & _
           "Data Source=Mydb;" & _
           "Initial Catalog=myDatabaseName;" & _
           "Integrated Security=SSPI"

cn.Open

strSQL = "Select * from Table1 JOIN Table2 on Table1.employeecode = Table2.employeecode JOIN Table 3 on Table2.functioncode = Table3.functioncode where Field3 = 'BARB'"

Set rs = ccn.Execute(strSQL)

That will get you everything from each of the related records in the JOINED tables.

AW
0
 
ChuckbuchanAuthor Commented:
for field3 there is only  part of the field that has a string "BARB", not a whole field. I guess there should be a function like MID$ function to extract that string from the content of the field3 which is size 30 and of text type.

for example record15  ,field3  =" sperBARBerouss"


this is why I thought we may go through the top of the recordset and keep testing field3 if there is a string "BARB" inside the content of that field we need to extract it and for instance write a message that says employeecode, employeename, has a string "BARB" then skip to the next record...

Does it make sens?

for your question above regarding linked tables. my answer is No I don't have any linked tables.


thanks


0
 
Arthur_WoodCommented:
you will need to add a REFERENCE to the ActiveX Data Object Library (Tools/References)

then you code might look like this:

Dim rs as Recordset
Dim cn as Connection
Dim strSQL as string

set cn = New Connection

cn.ConnectionString ="Provider=sqloledb;" & _
           "Data Source=Mydb;" & _
           "Initial Catalog=myDatabaseName;" & _
           "Integrated Security=SSPI"

cn.Open

strSQL = "Select * from Table1 JOIN Table2 on Table1.employeecode = Table2.employeecode JOIN Table 3 on Table2.functioncode = Table3.functioncode where Field3 LIKE '%BARB%'"

Set rs = ccn.Execute(strSQL)

this will extract all records that have BARB anywhere in the text of Field3.

AW
0
 
ChuckbuchanAuthor Commented:
I am not at the station where I need to test your code, but I will get back with you whenever possible to let you know if it has worked.

thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now