Basic Excel > Access Commands 1

[shavenlunatic]

Hi,

Been a long while since I've done this and just looking to get refreshed on some basic commands.

Now, the way I (think I) did this before was to dim access as an object and open the database in question (that much I think I'm fine with)

and im sure there was something along the lines of

dbapp.tblname.addrecord (field1, field2, field3)

but I can't remember. Baically, the only things I really need to know how to do are:

Add a record to a table
Find and Import the last record in table into a variable


erm, thats it. I have tried having a search around but all i seem to find is the ODBC stuff, and i'm fairly certain I didnt use anything like that before.

As usual, thanks in advance

________
clueless

 

[PHV]

I really think that you'd play with some Recordset objects.
Anyway, a starting point (typed, untested):

Set dbapp = CreateObject("Access.Application")
dbapp.OpenCurrentDatabase "\path\to\database.mdb"
dbapp.DoCmd.RunSQL "INSERT INTO tblname(numfield,datefield,textfield)" _
 & " VALUES(" & numvar & ",#" & datevar & "#,'" & textvar & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shavenlunatic]

thanks PHV, couldn't get what you posted working but it did point me in the right direction, so equally appreciated

I now have the following which receives data:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Documents and Settings\username\Desktop\excelaccess\textstore.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "tblstoretext", cn, adOpenKeyset, adLockOptimistic, adCmdTable

rs.Close
rs.Open "qrylastthree", cn
rs.MoveFirst
frmdatasender.lblreceived = ""
For testloop = 1 To 3
    frmdatasender.lblreceived = frmdatasender.lblreceived & rs.Fields("holdusername") & ": " & rs.Fields("holdtext") & Chr(13)
    rs.MoveNext
Next testloop
Set rs = Nothing
cn.Close
Set cn = Nothing

and to send:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Documents and Settings\username\Desktop\excelaccess\textstore.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "tblstoretext", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    With rs
        .AddNew
        .Fields("holdtext") = frmdatasender.txtsend
        .Fields("holdusername") = Windows.Application.UserName
        .Update
    End With
Set rs = Nothing
cn.Close
Set cn = Nothing

My only issue now is the Data Source setting, is there any way of setting that to be a url?

I tried

"Data Source=[URL unfurl="true"]http://website.whereitis.com/filelocation/filename.mdb;"[/URL]

and it errors as invalid filename. Is this possible or am I going to have to go down a different route?

________
clueless