Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i write records in access from excel vb code?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i write records in access from excel vb code?
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i write records in access from excel vb code?
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. "Patrick Molloy" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i write records in access from excel vb code?
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 "Ayaz Zanzeria" wrote: 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. "Patrick Molloy" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i write records in access from excel vb code?
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, "Patrick Molloy" wrote: 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 "Ayaz Zanzeria" wrote: 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. "Patrick Molloy" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating MS Access records with excel VBA + ADO | Excel Programming | |||
Copy Records from Access Database table(more than 5 lakh records in this table) to Excel Sheet | Excel Programming | |||
Getting records from Access 97 to Excel 97 | Excel Programming | |||
How to Get records from a query in access and put them in Excel | Excel Programming | |||
Can Excel write to an Access DB? | Excel Programming |