Connect to Database

I want to connect my macro with a table of access to fill my ComboBox but it doesn't work me

File is archivio.mdb

Table is commesse

Fiels is numero

Code is

Private Sub ComboBox1_Change()
Dim db02 As ADODB.Connection
Dim rs02 As ADODB.Recordset
Dim stringa As String

stringa = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
stringa = stringa & "D:\\archivio.mdb"
Set db02 = New ADODB.Connection
db02.ConnectionString = stringa
db02.Open


Set rs02 = New ADODB.Recordset
rs02.Open ("commesse", db02,adOpenDynamic,adLockOptimistic )
rs02.movefirst
Do While Not rs02.EOF
ComboBox1.AddItem rs02.Fields("numero")

rs02.MoveNext
Loop
rs02.Close
End Sub

Thanks

aLberto

SolidworksApi macros
Answers 12
SF
SF 2009-09-03 Best Answer

check connection string

check the database name, table name is coincident.

check other website for more referrence.

by the way, commessa<>commesse

SF
SF 2009-09-03
I'm not exactly positive, but I don't think you can connect to an Access DB on a FileShare even if it's a mapped drive.
SF
SF 2009-09-03
I have correct my post
JB
JB 2009-09-03

Do you get any sort of error?

Try getting rid of .fields, as in Recordset("Fieldname") instead of Recordset.Fields("Fieldname")

SF
SF 2009-09-03

Try an ODBC connection to .mdb access files

  1. Open the ODBC icon in your Control Panel.
  2. Choose the System DSN tab.
  3. Click on Add in the System DSN tab.
  4. Select the Microsoft Access Driver. Click Finish.
  5. In the next screen, click Select to locate the database.
  6. Give the database a Data Source Name (DSN).
  7. Click OK.
  8. then you can connect the ACCESS .mdb with its DSN
JB
JB 2009-09-03
This should not be necessary, and indeed will cause your code to only work on machines that have had this DSN set up.
SF
SF 2009-09-03

yes, it is only on the machine. this is the odbc shortage

but it is an easy start,right?

SF
SF 2009-09-03
What is the problem you are having exactly? Where does your code error out, or what behaves as it shouldnt?
SF
SF 2009-09-03

I think that the error is in this line

rs02.Open ("commesse", db02,adOpenDynamic,adLockOptimistic )

JB
JB 2009-09-03
Yes, you are right.  Remove the parenthesis from that line.
IK
IK 2009-09-03

Option Explicit

Private Sub CommandButton1_Click()
Dim db02 As ADODB.Connection
Dim rs02 As ADODB.Recordset
Dim stringa As String

stringa = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
stringa = stringa & "D:\archivio.mdb"
Set db02 = New ADODB.Connection
db02.ConnectionString = stringa
db02.Open


Set rs02 = New ADODB.Recordset
rs02.Open "SELECT numero FROM commesse", db02, adOpenDynamic, adLockOptimistic
rs02.movefirst
Do While Not rs02.EOF
    ComboBox1.AddItem rs02.Fields("numero").Value
    rs02.MoveNext
Loop
rs02.Close

End Sub

SF
SF 2009-09-04
  Thanks!!!!!