asked on

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

Arthur_Wood

what version of Access are you using. Are the Tables in SQL Server linked to your Access front end?

AW

Chuckbuchan

ASKER

I am using MSAccess 2000 and the tables are on the SQL server.

thaks

Arthur_Wood

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

AW

Arthur_Wood

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

Chuckbuchan

ASKER

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

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chuckbuchan

ASKER

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