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 have used the following example to create the database :

strDB = "D:\db.mbd" ' Create new instance of Microsoft Access.
Set appaccess = CreateObject("Access.Application")
appaccess.NewCurrentDatabase strDB
Set dbprinter = appaccess.CurrentDb
Set tbprinter = dbprinter.CreateTableDef("tb")

With tbprinter
.Fields.Append .CreateField("filed1", DB_Text)
.Fields.Append .CreateField("filed2", DB_Text)
.Fields.Append .CreateField("filed3", DB_Text)
End With
db.TableDefs.Append tb
db.Close

The database and the table is created fine, but how do I creates records and
then run my queries (the information to fill the querry is coming from a list
from LDAP) ? is there another better method to create the database and the
table (I'm under Excel2003, XP)

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

Hi

I have created a database which has 9 tables. In the same way I have an
excel sheet which has 9 sheets. Exporting from excel to access works
fine using VBA. But my problem is i need to write this code in each and
every sheet. And I have used 9 buttons in excel to upload the data in to
the database.
This is the code I used in VBA.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access
database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable

' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
..AddNew ' create a new record
' add values to each field in the record
..Fields("FieldName1") = Range("A" & r).Value
..Fields("FieldName2") = Range("B" & r).Value
..Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
..Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


IS THERE A WAY WHERE I CAN UPDATE THE COMPLETE 9 SHEETS TO THE
RESPECTIVE TABLES IN ACCESS IN A SINGLE CLICK.


--
bhellsun
------------------------------------------------------------------------
bhellsun's Profile: http://www.excelforum.com/member.php...o&userid=26798
View this thread: http://www.excelforum.com/showthread...hreadid=400424

bhellsun wrote:
I have created a database which has 9 tables. In the same way I have
an excel sheet which has 9 sheets. Exporting from excel to access
works fine using VBA. But my problem is i need to write this code in
each and every sheet. And I have used 9 buttons in excel to upload
the data in to the database.
This is the code I used in VBA.


IS THERE A WAY WHERE I CAN UPDATE THE COMPLETE 9 SHEETS TO THE
RESPECTIVE TABLES IN ACCESS IN A SINGLE CLICK.

No need to shout, I'm right here. Are all of your sheets set up the same?
What is the relationship between the sheet's names and the table's names?
Basically, you loop through all the sheets and inside the loop you add the
records. If your sheet names are the same as your table names, you can
substitute the sheet name for the table name in your sql statement. If not,
you may want an array that holds that info.

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

Thanks for the reply.
How do I loop through all the sheets ??
Yes your right there is a relation between the sheets and the tables
The name of the table is the same as the sheet.
And I have 9 different sheets and corresponding tables.
Can you give me a rought idea about the code

--
bhellsu
-----------------------------------------------------------------------
bhellsun's Profile: http://www.excelforum.com/member.php...fo&userid=2679
View this thread: http://www.excelforum.com/showthread.php?threadid=40042

Dim sh As Worksheet
Dim sSql as String

'Your existing connection code, but the rs.Open will go in the loop

For Each sh In ThisWorkbook.Worksheets

sSql = "SELECT * FROM " & sh.Name
rs.Open sSql, cn

r=3
Do While Not IsEmpty(sh.Range("A" & r))
With rs
'Your existing code for AddNew and Update
End With
Loop

rs.Close
Set rs = Nothing
Next sh

That's the rough skeleton. Post back if you need some details.

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

bhellsun wrote:
Thanks for the reply.
How do I loop through all the sheets ??
Yes your right there is a relation between the sheets and the tables.
The name of the table is the same as the sheet.
And I have 9 different sheets and corresponding tables.
Can you give me a rought idea about the code.

Thank you so much for your answer. I have a couple of question regardings
your example :

1)
This line adds fields with the Text type. What would be the correct syntax
for booleans (yes/no) and integer fields ?
sqlCreate = "CREATE TABLE tb (field1 char(50), field2 char(50), field3

2)
How do you insert values from variables or other objects values ?
sqlInsert = "INSERT INTO tb (field1, field2, field3) VALUES ('X', 'Y',
'Z')"

"Dick Kusleika" wrote:

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

stabilo wrote:
Thank you so much for your answer. I have a couple of question
regardings your example :

1)
This line adds fields with the Text type. What would be the correct
syntax for booleans (yes/no) and integer fields ?
sqlCreate = "CREATE TABLE tb (field1 char(50), field2 char(50), field3

http://www.pcreview.co.uk/forums/thread-1163197.php


2)
How do you insert values from variables or other objects values ?
sqlInsert = "INSERT INTO tb (field1, field2, field3) VALUES ('X',
'Y', 'Z')"

Dim var1, var2, var3

sqlInsert = "INSERT INTO tb (field1, field2, field3) VALUES ('" & var1 & "',
'" & var2 & "', '" & var3 & "')"

After VALUES, it reads: Open paren, single quote, double quote, ampersand,
variable, ampersand, double quote, single quote, comma, etc...

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