Need Help,, i m beginner in vba,, i'm writing a code for exporting the data from Userform to Access Database

i'm writing a code for transfer the data from Userform to Access Database,,please ...can any1 suggest me for coding for multi user to transfer the  data from userform to access data.....
Salman KhanAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
The problem I had with the API syntax was the PtrSafe keyword. It is only needed for 64 bit versions, and mine is 32 bit.

I cannot see any reason for the database inconsistency errors. The code seems to simply add a new row to a table.

It might be a problem with the drive that the database is on, so it could be worth trying on another drive and/or another server.

You have multiple users. This is sometimes problematic with Access databases, so it might be worth using transactions. I don't believe that your simple use needs it, but here is  link to Microsoft's MSDN entry in case you want to try it:
https://msdn.microsoft.com/en-us/library/ms680895(v=vs.85).aspx
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
Firstly, I think we need to clarify something. Is this code in an application that is not Access itself (hence UserForm) or is in Access, which doesn't have Userforms, because Access forms do the job?
1
 
Salman KhanConnect With a Mentor Author Commented:
hi,, thanks for the quick response,..i m created userform in Excel, and transfer userform data from excel userform to Access database,,, below are the code :-

Dim con As Object
Dim rs As Object

Set con = CreateObject("Adodb.connection")
con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny None;Jet OLEDB:Database Locking Mode=1;Data Source=\\mumchfs04\Serco Data\INFO Tool CRM\Backup\Old Folder\New folder (3)\CRM Data\Disposition_Report.accdb;Jet OLEDB:Database Password=didididi;")
If con.State = adStateClosed Then con.Open
If Me.Disposition.Value = "Call Back" Or Me.Disposition.Value = "Expected" Then

so many textbox values etc.

Set rs = con.Execute("insert into disposition_report(Company_Code, Disposition, Sub_Disposition, Comment, Called_By, Called_Number, Spoken_to, Callback_Date, Email_ID, New_Number1, New_Number2, Designation, New_EmailID ,InfoEmail1, InfoEmail2, InfoEmail3, InfoEmail4, Document1, Document2, Document3, Document4, Document5,Document6,RAG,Saved_Time)" + _
        "Values('" & CompanyCode & "','" & Disposition & "','" & SubDisposition & "','" & Comment1 & "','" & SavedBy & "','" & Callednumber & "','" & SpokenTo & "','" & Callbackd & "','" & EmailId & "','" & NewNum1 & "','" & NewNum2 & "','" & Desig & "','" & newemail & "','" & Infmail1 & "','" & Infmail2 & "','" & Infmail3 & "','" & Infmail4 & "','" & Doc1 & "','" & Doc2 & "','" & Doc3 & "','" & Doc4 & "','" & Doc5 & "','" & Doc6 & "','" & rag & "','" & SavedAt & "')")
If con.State = adStateOpen Then con.Close
Set rs = Nothing
Set con = Nothing
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
GrahamSkanConnect With a Mentor RetiredCommented:
Where have you placed your code and how is it called?

You will need some way of opening and filling the Userform. Is that already done?

A couple of points.

If you are a beginner, you might find it easier to use early binding. For this you will need to set references -  via Tools/References in the VBA IDE  - to libraries for any objects not already included. Excel will already have references to VBA, Excel itself, OLE, Office and Forms 2. You are using ADO, so the reference should be to the latest version of the
Microsoft ActiveX Data Objects 6.0 Library.

With early binding you get F1 context help on referenced objects. You will also be able to view and search the library members via the F2 key. 'Intellisense' will be active as well.

Early binding defines built-in constants like adStateClosed.

If you have Option Explicit (and you should) at the top of your code modules you will get an error: Variable not defined, If you don't, such numeric constants will be treated as 0.

I note your example shows the habit of setting object variables to Nothing before leaving the  procedure. This is quite unnecessary, since the objects will be destroyed automatically at the end of the procedure.
1
 
Salman KhanConnect With a Mentor Author Commented:
Thanks for all the tips..it work for me..but my major problem is:
1) I have 15 user at time to access the main database, they call the customer and tagged status on excel Userform >>from the excel userform its get import the data to access.accdb file; but sometimes its damaged the access file or locked.>> then i need to open the file  its show this error(Snap1) i just need a vba code  or solution to avoid this error.

Also i attached my dump (excel uefrom)
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
You haven't successfully attached anything. It is a three-stage process:

Attach File
Choose File
Upload File
1
 
Salman KhanConnect With a Mentor Author Commented:
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
Your .png file shows a message that starts:
"Microsoft Access has detected that this database in in an inconsistent start, and will attempt to recover the database." It goes on to say that it will make a backup copy and recover what it can , putting the recovered objects in the new file. It will report unrecovered objects in the "Recovery Errors" table.

Was the creation of the new database successful and is there anything in the "Recovery Errors" table?

The .xlsm file reports a "Compile error in hidden module: Module1" when it is opened. The code is password protected, so I can't diagnose that problem.
0
 
Salman KhanConnect With a Mentor Author Commented:
Was the creation of the new database successful >>>>>Yes we created the data and works good, but sometime we are getting this error.

and is there anything in the "Recovery Errors" table?>>> no we dont found anything in recovery table

The code is password protected, so I can't diagnose that problem.>>>>>>>I'm Extremely Sorry Password is =.?
dot and question mark
0
 
Salman KhanConnect With a Mentor Author Commented:
Hi Awaiting for reply, please help me
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
Sorry. I found problems with the syntax of the API calls and then got involved with something else.
I am looking at it again right now.
0
 
Salman KhanAuthor Commented:
thanks for all your assistant i appreciate your all feedback and quick response,,,,one more thing i want to know,,,is there any way to compact and repair data base using ADO ?
0
 
GrahamSkanRetiredCommented:
No but you can use  the ADO extension: Microsoft Jet OLE DB Provider and Replication Objects (JRO).

Details are in this Microsoft article:
https://support.microsoft.com/en-us/kb/230501
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.