Excel vba button error - links to Access tables (1 Viewer)

ralphyehle · Sep 14, 2010

Excel 2010 - Microsoft Access Driver question

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?

boblarson · Sep 14, 2010

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

zwik · Mar 4, 2011

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

boblarson · Mar 4, 2011

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:

Code:

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

zwik · Mar 7, 2011

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

zwik · Mar 7, 2011

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

boblarson · Mar 7, 2011

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.

zwik · Mar 8, 2011

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.

boblarson · Mar 8, 2011

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.

zwik · Mar 9, 2011

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

boblarson · Mar 10, 2011

zwik said:
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?

zwik · Mar 11, 2011

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.

boblarson · Mar 11, 2011

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?

zwik · Mar 12, 2011

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

boblarson · Mar 12, 2011

zwik said:
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.

boblarson · Mar 12, 2011

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

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

zwik · Mar 12, 2011

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

zwik · Mar 12, 2011

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.

zwik · Mar 14, 2011

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

Excel vba button error - links to Access tables (1 Viewer)

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Registered User.

Smeghead

Registered User.

Attachments

Smeghead

Registered User.

Attachments

Smeghead

Registered User.

Attachments

Smeghead

Registered User.

Attachments

Smeghead

Smeghead

Registered User.

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread