can u say how to write scripts in Ms Access?

[raghunadhs]

Hi All,
i have two databases. one is SQL server 2000 and another one is MS-Access.
initially I have same data in both the data bases. but now i made some changes in Sql Data base by writing some scripts (script contains sime update,select,insert statements and if clause.
I want to do the same changes on Access data base also. is it possible to write the scripts in Access-2003?
I tried with a simple example. i wrote an insert statement in a .txt file. now i am reading that query using "Module" in the Access. following is the example written in "Module" of Access Databse.

  
     Option Compare Database

  Sub macro1()
       Dim sFileText As String
       Dim strQuery As String
       iFileNo = FreeFile
       Open "c:\sample1.txt" For Input As #iFileNo
       Do While Not EOF(iFileNo)
           Input #iFileNo, sFileText
           strQuery = Replace(sFileText, "99", ",")  '--it replaces 99 with ,
          DBEngine(0)(0).Execute strQuery
       Loop
 End sub

since, while reading the data from the file, it is not accepting "," (comma), i used "99" instead of "comma" and replaced it again in the string.
for this simple statement, it is working fine. but it is not working for "if" statement ...etc.

so can u suggest me any method to write the scrits in Access 2003? my requirement is i should be able to update the Access database with minimum efforts by using the scripts what i alreay used for SQL.

thanks in advance...

[Hack]

What "If" statement isn't it working for?

[mendhak]

It's a strange post there, each time I read it I keep interpreting it as a different question. From what I gather, he has a SQL statement in c:\sample1.txt. He wants to read from that text file, into a string, and then execute that string (which is an INSERT statement) against the database.

Did I get that right?

[raghunadhs]

Yes mendhak. you are right. i want to read the sql statements from the text file and execute those statements in Access. is it possible for if
and select statements?

It's a strange post there, each time I read it I keep interpreting it as a different question. From what I gather, he has a SQL statement in c:\sample1.txt. He wants to read from that text file, into a string, and then execute that string (which is an INSERT statement) against the database.

Did I get that right?

[Hack]

An SQL query is simply a string, so that is easy enough, but, IF statements are VB specific conditional coding statements that you are not going to be able to execute as IF they were a string.

[raghunadhs]

hi Hack,
then can i use any record set in module? if so do you have any idea about syntax?

thanks...

An SQL query is simply a string, so that is easy enough, but, IF statements are VB specific conditional coding statements that you are not going to be able to execute as IF they were a string.

[anhn]

Can you post your actual query that you put in the text file?
You cannot use IF in Access query but you can use IIf().

[RobDog888]

Is the sql statement for the SQL database or the access database? There is some differences betwen the two.

[raghunadhs]

Hi,
the sql statement is for Access database. and in text file the query will be like this....

if ((select Marks from student where stuName ='Robert') < 50)
      Delete student where stuName='Robert'

Here STUDENT is a table.

Thanks.

Is the sql statement for the SQL database or the access database? There is some differences betwen the two.

[anhn]

I wonder how can you handle that 2 databases but don't know to write this simple query !!!

To delete all student records with Marks < 50:

DELETE student.* FROM student WHERE (student.Marks < 50)

To delete student Robert record if his Marks < 50:

DELETE student.* FROM student WHERE (student.Marks < 50) And (student.stuName = 'Robert')

[raghunadhs]

Hi anhn,
sorry for the delay.

I know how to write a delete statement. the code in my above post is an example for how i am using the IF statement. In my real application the table names, fields and the queries are different. (if i write the original query, it may confuse you so i wrote a simple if statement).

now i am using VB application with ADO dynamic objects to run the queries.
i am executing the queries as below

rstRecordSet.Open strSql, conConnection, adOpenStatic, adLockBatchOptimistic

as our friends told in above posts....., it may not be possible to use IF statements in Queries....
If it is possible, please let me know it.
Thanks in advance...

[QUOTE=anhn]I wonder how can you handle that 2 databases but don't know to write this simple query !!!