This really is fundamental stuff, but I am somewhat not really acquainted with VBA and also the Word/Access object models.
I've got a two column database around 117000 records. The posts are 'surname' and 'count'. I would like a person to have the ability to type Cruz inside a textbox striking submit. Then i wish to run something similar to
SELECT table.count FROM table WHERE surname = string
and return the need for table.count inside a string.
It feels as though this ought to be 5 or 6 lines of code (which i've but will not publish) but I am clearly missing something!
To begin with, be cautious naming the column 'count' -- this can be a keyword in SQL and can create problems. Similarly, don't call the table 'table'.
Here's some sample code which shows just one way of doing the work:
' This example uses Microsoft ActiveX Data Objects 2.8, ' which you have to check in Tools | References ' Create the connection. This connection may be reused for other queries. ' Use connectionstrings.com to get the syntax to connect to your database: Dim conn As New ADODB.Connection conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\tmp\Database1.accdb" Dim cmd As New ADODB.Command Set cmd.ActiveConnection = conn ' Replace anything which might change in the following SQL string with ? cmd.CommandText = "select ct from tbl where surname = ?" ' Create one parameter for every ? Dim param As ADODB.Parameter Set param = cmd.CreateParameter("surname", adBSTR, adParamInput, , TextBox1.Text) cmd.Parameters.Append param Dim rs As ADODB.Recordset Set rs = cmd.Execute MsgBox rs("ct") rs.Close conn.Close
You'll be able to use InsertDatabase:
Sub GetData() ActiveDocument.Bookmarks("InsertHere").Select Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _ Connection:="TABLE Members", SQLStatement:= _ "SELECT [Count] FROM [Members]" _ & " WHERE Surname='" _ & ActiveDocument.FormFields("Text1").Result & "'", _ DataSource:="C:\docs\ltd.mdb", From:=-1, To:= _ -1, IncludeFields:=True End Sub
It is really an edited macro recorded while using database plugin.
EDITED Warning: this code, as proven, is susceptible to a SQL Injection attack.