upgraded Excel to 2010 and Access to 2007 from 2003 and now this code produces error message: [Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified - How do I fix this?

Private Sub btnPageLoad_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo Err_btnPageLoad_DblClick

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.accdb)}; DBQ= S:\QCR\Worksheet\Data\QCRData FY2011.accdb"

' Replace actual Access file path here - I changed the (*.accdb) back to (*.mdb) and the Access file back to .mdb and was able to export okay. Problem seems to be with the Driver={Microsoft Access Driver (*.accdb} which isn't recognized. What's the problem?

This site is a good one to bookmark. It has connection strings galore.

See here for Access 2007/2010

http://www.connectionstrings.com/access-2007

I am facing same problem. I have a form with two text boxes and a button to send the data into a ms access table. I am getting "Run-time error" object required. My code is as follows.

Dim con As New ADODB.Connection

Dim connectionString As String

Dim sql As String

connectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\project.accdb;"

con.Open connectionString
sql = "insert into tbl_name (firstname, lastname) values('" & TextBox1.Text & "', '" & TextBox2.Text & "')"
con.Execute sql
MsgBox "Values entered", vbInformation
con.Close
Set con = Nothing

I have office 2007 installed, i have reinstalled it again but the error is still there. Will appreciate if someone give the solution

How about using DAO instead (it's a lot simpler). You need to set a DAO reference (for Access 2007 it would be Microsoft Office 12 Access Database Engine Object Library)

and then the code would be:

Dim db As DAO.Database
Dim strSQL As String

Set db = OpenDatabase("C:\project.accdb")

strSQL = "Insert INTO tbl_name (firstname, lastname) values('" & TextBox1.Text & "', '" & TextBox2.Text & "')"

db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

Thanks Bob larson,

I will apply your code and see if it works. I hope it will work. I will get back when i test the code.

Thank you again for the help

Best Regards

Arshad

Hi Bob larson,
I have tested your code. First I have saved the excel file with new name. Then i deleted the old code from vba and put your new code. I have referenced DAO (Microsoft Office 12 Access Database Engine Object Library). When i ran the code it gave following error.

Run-time error '424':

Object required

I dont know what cause this error. I am a newbie. I will appreciate further help in this regard.

Best Regards

Disable any error handler by commenting it out and then run it and when you get the error click on the DEBUG button and it should highlight the line of code which has the problem. Post that here.

I have uploaded two jpg file to have an idea of the error. This is a test project. I have to make the project later on when it is successful. The excelproject.jpg file shows the highlighted line where the error occured.

It's unfortunate that you did not post a screenshot showing the ENTIRE line which caused the error. But check to make sure your text boxes are named Exactly Textbox1 and Textbox2.

I have checked the text boxes name and they are correct. I am attaching the error jpg it may help.

I have checked the text boxes name and they are correct. I am attaching the error jpg it may help.

Is your table name really tbl_name with the tbl and underscore?

Yes my ms access database table name is tbl_name and has two fields i.e firstname and lastname. I have attached an image of database in design view to give u an idea.
When i open the excel file, it give me security warning, that some active content has been disabled. By pressing option button right next to it give me option to enable it. After enabling it i run the code and gives an error.

Well, the code I see (and remember you cut it off so I couldn't see the end of the line on the line which errors out) doesn't look wrong in any way. Can you post a screenshot of the entire code of that line?

Also, is there data in both text boxes?

If possible, can you upload a copy of the Excel sheet and a copy of the database so I can try it out and see what's going on?

Ok i will upload the copy of excel file and access file so that you can test it out what is wrong with it. My excel file has xlsm format but i have saved it as xls format due to format restriction in the forum upload.
Hope it will help you in finding the error. Thanks in advance

Ok i will upload the copy of excel file and access file so that you can test it out what is wrong with it. My excel file has xlsm format but i have saved it as xls format due to format restriction in the forum upload.
You could have uploaded the xlsm file. All you have to do is zip the file and upload the zip file.

Well, that did help. The code should be this instead:

strSQL = "Insert INTO tbl_name (firstname, lastname) values('" & Sheet1.TextBox1.Text & "', '" & Sheet1.TextBox2.Text & "')"

Ok i am uploading zip format of my original excel file.

Thanks a lot boblarson. You are great. You have solved my problem. I was going nuts where the error is. I give you 5 star out of 5.


Thanks a lot.

This is the solution form Bob Larsonfor other viewers

Well, that did help. The code should be this instead:

strSQL = "Insert INTO tbl_name (firstname, lastname) values('" & Sheet1.TextBox1.Text & "', '" & Sheet1.TextBox2.Text & "')"

Best of luck