stabilo wrote:
I need to createa new database called db.mdb that will contains a
table call tb with a few fields (field1, field2, field3).
Then, I need to be able to add records in this table. Later in my
program, I need to run an SQL query that will returns all records if
field1 is = to "X" (then I need to list the resulting data)
I have many topics about my questions but never managed to make it
work.


I don't use Access.Application to create dbs and tables. I use ADOX to
create the tables, then use ADO to create tables, add records, and retrieve
records. You need to set a reference to both the Active X Data Object
library and the ADO Ext. library. Here's an example of how to do that
stuff:

Sub MakeDatabase()

Dim axCat As ADOX.Catalog
Dim adCn As ADODB.Connection
Dim adRs As ADODB.Recordset
Dim sConn As String
Dim sqlInsert As String
Dim sqlFieldX As String
Dim sqlCreate As String
Dim sPath As String
Dim sName As String
Dim sMsg As String

sPath = "C:\Dick\"
sName = "db.mdb"

'create new database
Set axCat = New ADOX.Catalog
axCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=4;Data Source=" & sPath & sName

Set axCat = Nothing

'connect to datatbase
sConn = "DSN=MS Access 97 Database;"
sConn = sConn & "DBQ=" & sPath & sName & ";"
sConn = sConn & "DefaultDir=" & sPath & ";DriverId=281;"
sConn = sConn & "FIL=MS Access;MaxBufferSize=2048"

Set adCn = New ADODB.Connection
adCn.Open sConn

'Create a table
sqlCreate = "CREATE TABLE tb (field1 char(50), field2 char(50), field3
char(50))"

adCn.Execute sqlCreate

'add two records
sqlInsert = "INSERT INTO tb (field1, field2, field3) VALUES ('X', 'Y',
'Z')"
adCn.Execute sqlInsert

sqlInsert = "INSERT INTO tb (field1, field2, field3) VALUES ('A', 'B',
'C')"
adCn.Execute sqlInsert

'Retrieve records with X
sqlFieldX = "SELECT * FROM tb WHERE field1='X'"

Set adRs = New ADODB.Recordset
adRs.Open sqlFieldX, adCn

'dispaly the records in a message box
Do Until adRs.EOF
sMsg = sMsg & adRs.Fields(0).Value & vbTab & _
adRs.Fields(1).Value & vbTab & _
adRs.Fields(2).Value & vbNewLine

adRs.MoveNext
Loop

MsgBox sMsg

adRs.Close
adCn.Close

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com