can u say how to write scripts for MS- Access

[raghunadhs]

Hi All,
i have some simple scripts( which may have some insert statements, update statements etc). these scripts were written in Sql server 2000. now i want to use the same scripts in Access. (since the data is same in both Access, Sql server databases).

can i write the scripts in Access-2003? if so please help me.
Actually i tried with simple examples. it is working fine for insert, update statements. but it is not working for "If" state ment, and Select statement.

i tried it by using "modules" in the access data base. in module i created macro. in that macro, i am reading the queries from a .txt file with the help of Vb syntax, and am executing that staetement.
i wrote as.....

 
     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", ",")
          DBEngine(0)(0).Execute strQuery
       Loop
 End sub
 

while reading the data from .txt file, the input method is unable to read the entire line due to the commas (,) so i used 99 instead of ,
it is working fine for insert, update statements.
but if i write some if statements, in the .txt file it is giving the error.

Please suggest me any way to write the scripts.

thanks in advance...

[jaxjagfan]

Just a thought...

If both your Access and SQL tables contain the same data

Why not leave it in SQL and link to the table?
Why not use DTS to update the Access database if linking isn't an option?

[raghunadhs]

Hi jaxjagfan,
i want to do few changes only on the Access database. for those few changes i have my own scripts written in Sql server 2000. so i want to run those scripts in Access.
and i don't know about DTS .

can i write and run the scripts in Access ?
if so please let me know how to do?

Thanks in advance....

Just a thought...

If both your Access and SQL tables contain the same data

Why not leave it in SQL and link to the table?
Why not use DTS to update the Access database if linking isn't an option?