• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform Data to store in access ... new database connection error

S

sagarrajula

New Member
Hi Everyone,


I have created a userform where i want the data to be stored in access automatically,


However i have written some code, where in start only i m getting error at the


Dim con as new adodb connection


Can some please tell me why is this error occurring, or how to come overview with it


Thanks in advance,
 
Hi, sagarrajula!


As it is written it seems to be at least a syntax error, try with:

Dim con as New ADODB.Connection

but only if you have the proper library referenced: Microsoft ActiveX Data Objects 6.1 Library (C:program Files x(86)Common FilesSystemadomsado15.dll).

Regarding your actual version of Excel and Office it might slightly change but I hope you'd find it out.


If not try with this:

Set cn = CreateObject("ADODB.Connection")


Otherwise consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!


PS: References are added/removed from the VBA editor (Alt-F11), Tools, References, and then selecting, deselecting or looking for new entries.
 
Hi,


Thank you very much for your update,


However I need to check the syntax error.


Meanwhile, Just an help needed...


I have created a data entry user form in Excel as a front end, and I want the userform data to be stored in the Microsoft access database.


is it possible that the userform excel can be on a shared mode, and if I save on a local server path, and at a single time 4 or 5 persons can save a new data ....


Will these 4 or 5 new data will reach a database and create and new rows.... or will it be overridden on the same row for 5 times..


I am very much confused on this...


Please help me....
 
Hi, sagarrajula!


Lem me see if I understand:

- you have an Excel workbook with an user form

- you uses VBA code to manipulate (add, update, delete) data from a shared Access Database

- you want your Excel file to be used by many users concurrently


You have 2 choices:

a) copy the workbook to all workstations (all will be accessing the shared database)

b) move the workbook to a shared folder (maybe same as DB or not)


In both cases it'll all depend on how do you access (read, update, write) your data, on how is the source DB table organized, on which key values it has, on many things, ...


Hope it helps.


Regards!
 
Hi,


Thank you very much for the update,


However, i am happy atleast we can do the 1 or 2nd option shared by you.


Let me explain, what i m doing & expecting:


I have created a userform named: PODetails, and 2 Text boxes named,1:No_Lines_Processed,2:total_PR


Now i have created a database on my desktop called test.accdb


Now i have added some code to the userform save command, but its not working,


Where i m not getting any error and however it is not saved on the database file.


Mentioned is the code for your reference and help required.


Private Sub CommandButton1_Click()

'Initialize all variables

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim stDB As String, stSQL As String, stProvider As String

Dim orderNum As String

Dim orderDate As String


stDB = "Data Source= C:Documents and SettingsSagarDesktoptest.accdb"

stProvider = "Microsoft.ACE.OLEDB.12.0"


'Opening connection to database

With cn


.ConnectionString = stDB

.Provider = stProvider


End With


'SQL Statement of what I want from the database

stSQL = "INSERT INTO PO_tbl (No_Lines_Processed, total_PR) " & _

"Values ('" & No_Lines_Processed.Value & "', '" & Total_PR.Value & "')"


Set rs = Nothing

Set cn = Nothing


End Sub


Also want 2 things to get confirmed:

1: If i save a workbook on the common server local path, on a shared workbook mode, will the other users can access the userform and enter the data on a real time basis in the access database(Which should not get overrride, should be a unique values)


2: what do you mean by shared database ?


Many Many thanks for you help in advance.
 
Hi, sagarrajula!


About your code...

Consider uploading the sample files (workbook and database, even with dummy data), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


About your questions...


1) Yes, you can. Besides, does this help?

http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010342985.aspx

Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Userform%20Data%20to%20store%20in%20access%20...%20new%20database%20connection%20error%20%28for%20sagarrajula%20at%20chandoo.org%29.xlsm

Open it, click on command button, an empty userform will be loaded, just click on it and check column A: each time it stamps the actual date & time in first free cell.

Now go to tab Review (or whatsoever is its name in English versions, it's the tab from where you protect sheets and books), Changes group, click on Share Workbook, check Allow Multiuser Update and click on Accept, the book will be saved.

Then start a new instance of Excel (right button on a shortcut and select MS Excel from almost the bottom entry, or run another copy from the start menu) , open the workbook.

You'll have two copies of Excel running the same shared workbook, data in column A of both files should be the same. Adjust window sizes to see both at the same time, click on seldom command buttons to load the form, and begin clicking on each user form in a random way (x times on first, y times on second, and so on).

You'll notice that you can use the same form from the same shared workbook from two locations, but in this basic example there's a conflict on where to write the data, but if you use a DB connection and perform the proper validations it works.

Hope it helps.


2) A shared DB is a database placed in a network location that can be simultaneously accessed by more than one user.


Regards!
 
Hi,


Thank you for the help,


http://rapidshare.com/files/2733157737/Test%20DB.xls

http://rapidshare.com/files/3986390986/Test.accdb.mdb


These 2 are excel and Access database, where i want the userform textbox data to store directly in access....


I hope u came to know now what is my rquirement,


Many thanx in advance.
 
Hi Sir,


Links revised :


http://www.2shared.com/document/asUBljxs/Test_DB.html


http://www.2shared.com/document/-lPN5l69/Testaccdb.html


Hope so now it will work ...


Thanks,
 
Hi, sagarrajula!


Update your "OK" violet command button code in userform as follows:

-----

[pre]
Code: 
Option Explicit

Private Sub CommandButton1_Click()

'Initialize all variables
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stDB As String, stSQL As String, stProvider As String
Dim orderNum As String
Dim orderDate As String

stDB = "Data Source= C:Documents and SettingsSagarDesktopPtest.accdb"
stProvider = "Microsoft.ACE.OLEDB.12.0"

With cn
' Opening connection to database
.ConnectionString = stDB
.Provider = stProvider
.Open

'SQL Statement of what I want from the database
stSQL = "INSERT INTO PO_tbl (No_Lines_Processed, total_PR) " & _
"Values ('" & No_Lines_Processed.Value & "', '" & Total_PR.Value & "')"

' executing command
.Execute stSQL

End With

Set rs = Nothing
Set cn = Nothing

End Sub
[/pre]
-----


You were missing the execution of the SQL command defines. I also added an "Option Explicit" statement just for best practices.


You may as well clear the input text boxes after insertion on Access table, so as to not get confused.


Regards!
 
Hi Sir,


Thank you so much sir,


However i am getting an error at .open Statment,


Errr:-could not found the file, also the file path is correct as i have checked 3times


Can u also please help me on the same.


MANY THANKS IN ADVANCE.
 
Hi, sagarrajula!

When I tested your uploaded database I had to change the name to test.accdb.mdb and perhaps you have to do so.

Regards!
 
Thank you sir for your kind help......


It seems to be working now, However i have many fields and many textbox and date_picker which i want to add in this coding ...


Can you help me with any of such examples or website which i can refer ...


This code is to add new data to the database.


1:however i would also need a code for View and edit the existing data and then save

2:A code for which all the existing Data in the table to be viewed in the Listbox based on 2 or 3 option buttons....
 
Hi, sagarrajula!


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/forums/topic/using-access-to-store-data-for-excel-dashboard

http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/

... and the is long.


Regards!
 
Hello Sir,


Again i m getting the erro at .open and msg coud not find the file


However as instructed by you, i have changed to .mdb and checked the address of the data source and its correct.... It would be really great if you help on the same.


Many thanks
 
Hi, sagarrajula!


First of all check in your Windows Explorer if you have hidden known file extensions. From the WE, first entry in gray bar (Organize or something alike), Folder & Search Options, 2nd tab View, and look for the entry "Hide file extensions for known file types" and uncheck it if checked, and Accept.


Now from the same WE go to the folder where your Access file is stored and verify the full name with the proper extension.


With that value update if necessary the value of the string variable stDB and then run the process.


If any error arises please write it down complete, paste it here and upload the related files (Excel workbook and Access database).


Regards!
 
Hellooo Sir


Thank you for the update,


I have tried the things which u have said, but its not working, i m really shocked.


And i am attaching my 2 files. access and excel file to show you the exact problem


http://www.2shared.com/file/zD6OFvAx/SAMI_Tracker-Programme.html

http://www.2shared.com/file/XkYQz361/SAMI.html


And my Error occurs at .open (Could not open the file)


Hope you can really help with this error.


Thanks again sir for your kind help.
 
Hi, sagarrajula!

Change these two lines from:

stDB = "Data Source= E:SagarDesktopSAMI.accdb.mdb"

stSQL = "INSERT INTO Data_Entry (CustomerName, EmailSubjectLine) " & _

"Values ('" & SAMI_Tracking.DataEntry.basic_details.cst_Name.Value & "', '" & SAMI_Tracking.DataEntry.basic_details.Email_Sub.Value & "')"

to:

stDB = "Data Source= E:SagarDesktopSAMI.accdb"

stSQL = "INSERT INTO Data_Entry (CustomerName, EmailSubjectLine) " & _

"Values ('" & SAMI_Tracking.Cst_Name.Value & "', '" & SAMI_Tracking.Email_Sub.Value & "')"

Regards!
 
Hello Sir,


Tried the statements given by you. still could not open the file.


Can you let me any other option. or else which references are to be checked.


Many thanks.
 
Hi, sagarrajula!


You'd be writing something wrong as I tried here downloading your files and changing those sentences. If you didn't change anything from your uploaded files it should work, did you modify anything after uploading them?


Basically it's:

a) changing the database name from SAMI.accdb.mdb to SAMI.accdb

b) changing the qualification prefix of the controls name from SAMI_Tracking.DataEntry.basic_details to SAMI_Tracking


Regards!
 
Sir Thank you very much again ... now its working fine....


however i m not getting the code for all the data of the same table to view in the list box of the userform and sorted descending order by date in excel ... and based upon 2 option buttons it should work... where if a user clicks on the option button, then it must read the combobox selection and it must filter in the specific coloumn returning all the data which is in the table.... another wherein if a user clicks on a second option button then it must return all the data in the same table.
 
Hi, sagarrajula!


I didn't checked all your workbook's user forms and the code behind its controls, I just fixed up your original connection string issue with the Access database and your original SQL instruction syntax issue to get the data properly inserted into a table.


Now it seems as if you need further help with building code for two options buttons to sort data upon different criteria, but I found no code associated with them.


Fortunately as you posted the links to both files, Excel workbook and Access database, surely or very probably people who read this maybe would come up with a solution; particularly in my case it's just a matter of time.


In the meanwhile if I were you I'd begin reading the topics posted at this link from upwards:

http://chandoo.org/forums/topic/userform-data-to-store-in-access-new-database-connection-error#post-98973


I'd begin, go through, and finish all them. There you'll find a kick off for understanding the basics for the project you seem to be intending to build. Besides this forums where you could find answers to specific issues or problems (but not always full project development) it's advisable for you to give a look at the available courses in this website:

http://chandoo.org/wp/welcome/


Regards!
 
Topic crossposted and closed at:

http://chandoo.org/forums/topic/on-double-click-listbox-data-on-excel-userform-return-the-records-of-access-db

Keep on with this thread.
 
You must log in or register to reply here.
Back
Top