how do i write records in access from excel vb code?

[Ayaz Zanzeria]

I have designed a reports dashboard using excel vba. I want to track the
number of times this dashboard along with the report name which has been
accessed. I have created a database in access with the following fields:
Access_Date: Date/Time
Access_Time: Date/Time
User_Name: Text
Computer_Name: Text
Report_Accessed: Text
Please help with sample code in excel vb that will work in excel 2003.
Thanks in advance.

 

[Patrick Molloy]

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "insert into BondTable ( [Currency] ,[Security] ) values
('USD','Trsry 4 2018')"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.close
Set con = Nothing

End Sub

 

[Ayaz Zanzeria]

Hi Patrick,

Thank you for the immediate response. However, I have tried this code and
this does not work in Excel 2003 and above. This gives me an error especially
at ADODB.connection stating that this Object or Property is not supported.
The library does not show ADODB at all. This code works fine in excel
versions prior to excel 2003. Please help with code that will work with excel
2003 and above.

Thanks in advance.

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "insert into BondTable ( [Currency] ,[Security] ) values
('USD','Trsry 4 2018')"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.close
Set con = Nothing

End Sub

I have designed a reports dashboard using excel vba. I want to track the
number of times this dashboard along with the report name which has been
accessed. I have created a database in access with the following fields:
Access_Date: Date/Time
Access_Time: Date/Time
User_Name: Text
Computer_Name: Text
Report_Accessed: Text
Please help with sample code in excel vb that will work in excel 2003.
Thanks in advance.

 

[Patrick Molloy]

I wrote for Excel 2003 and it riuns in 2007 also
make sure that in the code editor (IDE) that you have a reference to the
Microsoft Active Data Objects 2.6 Library

Hi Patrick,

Thank you for the immediate response. However, I have tried this code and
this does not work in Excel 2003 and above. This gives me an error especially
at ADODB.connection stating that this Object or Property is not supported.
The library does not show ADODB at all. This code works fine in excel
versions prior to excel 2003. Please help with code that will work with excel
2003 and above.

Thanks in advance.

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "insert into BondTable ( [Currency] ,[Security] ) values
('USD','Trsry 4 2018')"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.close
Set con = Nothing

End Sub

I have designed a reports dashboard using excel vba. I want to track the
number of times this dashboard along with the report name which has been
accessed. I have created a database in access with the following fields:
Access_Date: Date/Time
Access_Time: Date/Time
User_Name: Text
Computer_Name: Text
Report_Accessed: Text
Please help with sample code in excel vb that will work in excel 2003.
Thanks in advance.

 

[Ayaz Zanzeria]

Hi Patrick,

Thank you very much for your help on this.

Please can you also provide the code snippet in which I give the reference
to the Microsoft Data Objects 2.6 library?
Is it an additional add in that I have to install?
The folder that has my database, has read only access for the other users.
Will this code still work?

Please help on the above.

Thanks in advance.

Regards,

I wrote for Excel 2003 and it riuns in 2007 also
make sure that in the code editor (IDE) that you have a reference to the
Microsoft Active Data Objects 2.6 Library

Hi Patrick,

Thank you for the immediate response. However, I have tried this code and
this does not work in Excel 2003 and above. This gives me an error especially
at ADODB.connection stating that this Object or Property is not supported.
The library does not show ADODB at all. This code works fine in excel
versions prior to excel 2003. Please help with code that will work with excel
2003 and above.

Thanks in advance.

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "insert into BondTable ( [Currency] ,[Security] ) values
('USD','Trsry 4 2018')"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.close
Set con = Nothing

End Sub

:

I have designed a reports dashboard using excel vba. I want to track the
number of times this dashboard along with the report name which has been
accessed. I have created a database in access with the following fields:
Access_Date: Date/Time
Access_Time: Date/Time
User_Name: Text
Computer_Name: Text
Report_Accessed: Text
Please help with sample code in excel vb that will work in excel 2003.
Thanks in advance.