I have this problem I can not seem to be able to resolve. It is urgent that I fix it for my project.
The code bellow is a test: It is to pick up the data from Titles tables in db1.mdb in the c:\ and put in the excel sheet. However it does not work and I am stuck. Please somebody help me.
I have MS DAO 3.5 Object Library.
also, when highliting the db it is set at 'nothing' I don't understand.
Private Sub CommandButton1_Click()
On Error Resume Next
Dim db As Database
Dim rs As Recordset
Dim fld As Field
Dim i As Integer
Set db = OpenDatabase("c:\db1.mdb")
Set rs = db.OpenRecordset("Titles", dbOpenDynaset, dbReadOnly)
For Each fld In rs.Fields
i = i + 1
Cells(1, i).Value = fld.Name
Can you give me more code, that is, can you take the code above and use y our your syntax bellow?
Dim conn As New ADODB.Connection 'Excel have this connection object... and is not the same
conn.Provider = "Microsoft.Jet.Oledb.3.51" ' set the good version of jet engine
conn.ConnectionString = "C:\toto.mdb"
Can you tell me all the 'References' you have for you code. I think my problem is that either one is either missing or is invalid:
Bellow are the 'References' I have:
VB for application
MS Excel 9.0 Object Library
MS Office 9.0 Object Library
MS DAO 3.6 Object Library
MS Forms 2.0 Object Library
MS Access 9.0 Object Library
MS ADO Ext. 2.7 for DDL and Security
MS ActiveX Data Object Recordset 2.7 Library
Anyway Here is the code:
When I debbug my code, I get this error :
'User-defined type not defined
Public Sub UpdateDB()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
conn.ConnectionTimeout = 30
Set cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE LOGCALL_table SET LOGCALL_table.clientName = '" & ActiveSheet.Range("A" & i).Value & "' WHERE LOGCALL_table.clientName = 'RICHARD TEST2';"
cmd.Execute , , adCmdText