By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

can u say how to write scripts for MS- Access

P: 52
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.....
Expand|Select|Wrap|Line Numbers
  1.  
  2.      Option Compare Database
  3.  
  4.   Sub macro1()
  5.        Dim sFileText As String
  6.        Dim strQuery As String
  7.        iFileNo = FreeFile
  8.        Open "c:\sample1.txt" For Input As #iFileNo
  9.        Do While Not EOF(iFileNo)
  10.            Input #iFileNo, sFileText
  11.            strQuery = Replace(sFileText, "99", ",")
  12.           DBEngine(0)(0).Execute strQuery
  13.        Loop
  14.  End sub
  15.  
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...
Jan 9 '08 #1
Share this Question
Share on Google+
2 Replies


jaxjagfan
Expert 100+
P: 254
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?
Jan 9 '08 #2

P: 52
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?
Jan 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.