Running and executing queries in code in mdb

[forweefolks]

Hi there,

I'm desperate to find a solution to a problem I'm currently battling with. I've been looking for a way to run and execute queries based on sql statements in VB without upgrading the access database (.mdb) into an access project (.adb).

endless hours with the help have led me to think that i'am getting nowhere with this approach. I know that in access projects using DAO or ADO (lost track of these abbreviations a long time ago), one could run queries based on sql-statements. Is there a way to do something like that in .mdbs? Please, tell me there is, if not, we'll have to upgrade.

thanks in advance,

Mika

 

[tlbroadbent]

You can easily create VBA modules in MDBs and execute SQL. Take a look at DoCmd.RunSQL and DoCmd.OpenQuery in Help. Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[THoey]

Maybe I am not following what you are actually trying to do here. When you open your MDB, and select the modules tab, you could create a function similar to this example that I have that removes extra spaces from a field in a database (Access97):

Public Function RemoveSpaces()
    Dim db As Database
    Dim rst As Recordset
    Dim stringin As String
    Dim pos As Integer
        
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from RemoveSpaces")
    
    rst.MoveFirst
   
    Do While Not rst.EOF
    
        rst.Edit
        stringin = rst!string1
        
        Do While InStr(1, stringin, "  ")
            pos = InStr(1, stringin, "  ")
            stringin = Left(stringin, pos - 1) & " " & Mid(stringin, pos + 2)
        Loop
        
        rst!string2 = stringin
        rst.Update
        rst.MoveNext
       
    Loop
    
End Function

Does that give you an idea of what you need to do? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[arielkoren]

Hi;
I would like to use vba to send mail.
My problem is that I need to change the from field so it will be sent from another account.
I have all the permissions to do so but I don't now how to do it.
Can some one help me ?
Thanks;
Ariel

 

[tlbroadbent]

Ariel,

Please post your question in a new thread rather than adding it to a thread that has been inactive for 9 months. This is especially important when a new question isn't related to the subject of the thread. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cmmrfrds]

The difference in the connection between the adp and mdb is that in the mdb file the activeproject.connection will be the mdb file itself. In the adp the activeproject.connection will be the connection to the sql server database. While in an mdb, you can create a connection to the sql server which you need to establish in code. This is not difficult, but if you need to join an mdb table and sql server table you will need to link the sql server table and then you can refer to both with the activeproject.connection.

Unless you need to join local mdb tables to sql server tables, it is probably better in the long run to create an adp. If this is a new project then go with the adp.

To work with datasource, DAO has been around longer than ADO but ADO is recommended by Microsoft and will be enhanced, whereas, DAO will probably gradually fall into disuse. If you plan on working with a web interface, what you learn in ADO will be transferable.