Import export to and from access databases Results

Hello everyone,
I am stuck working with what I believe is a very old access database (I think it's access 2.0) that I am struggling to open.

I have a program front-end that interfaces with this database. When I launch the program front-end it prompts me for a user name and password, which I enter, then the program opens and I can access the data from within the program.

However, when I try to open the access database directly, I receive this error:
Code: You do not have the necesary permission to use the "my_database.mdb' object. Have your system administrator or the person who created this object establish the appropriate permission for you. I did some searching and found this is usually related to workgroup security and involves a system.mdw file. I have tried searching my entire computer for this file and cannot find it.

I am able to open the database via the front-end application using the user name and password, but unfortunately this program does not allow me to export the data.

The bottom line is the front-end application is being sunsetted, and I need to extract the information from this database. I've also tried to attach the .mdb file as an ODBC source and connect to it, but again I received this same error. I've also tried opening a new database in Access and importing from the old file, but again I got the error.

It is imperative that I retrieve this data. If anyone has any ideas, I would be very appreciative. Thank you very much.

There are 20 employees that stretch throughout state that record data on damage caused by blasting. Management wants this data in one database however they have no money for a license of either SQL or Oracle. Can't the data from these individual databases be exported as an xml file and then imported into a single collective Access database?

My office is in the process of switching from Open Office Base to Microsoft Access on the computers. I manage a team of about 12 agents who enter in information into a form(currently in Base but shortly in Access) throughout the day. Each computer has it's own database right now. I then export the data from their database(daily) into an Excel spreadsheet and import that into Microsoft Access on my computer, which is what I run all of our reports off of. I'm wondering about some of the possible options of streamlining this sort of process once we have Access on all the computers. Basically, i'm looking for better ways for my team to input data and for that data to get to my database so I can run reports. What are some of the possible options with this number of agents? Are there good ways to link their databases with mine or set up forms that automatically enter data into my database? Any ideas would be appreciated! Thanks.

All

I need to import Outlook email in an Access 2007-database.

I found code how to read mails stored in the Inbox
Set golApp = Outlook.Application
Set gnspNameSpace = golApp.GetNamespace("mapi")
Set mapifolder = gnspNameSpace.GetDefaultFolder(olFolderInbox)
For Each item In mapifolder.Items
MsgBox item.Subject
Next

Problem is that my mails are not stored in the Inbox. They are stored in a folder "Export" which I have created manually in my Outlook mailbox.

How can I access a non-default Outlook folder and retrieve its content?

Kind regards
Ino

Is there a way to export multiple reports from Access into ONE Excel Spreadsheet? I have a database that houses supplemental health insurance information. The company wants a "final report" that will include information from prior years for quoting purposes. The "final report" currently resides in Word and has references to an Excel Spreadsheet, so is there a way to set up some code to export the reports all into one spreadsheet? The reports have vastly different information in them so there is no way to have it all in one big report.

Also, is there an easy way to import a Word document into Access as a report and keep the formatting (such as the bullet points)?

Thanks for any help you can give!!!

Hello

Please excuse me if this is in the wrong area. I am attempting to solve this problem with VBA so I figured this might be the place.

I work on an Access 2007 database that has some pretty complex queries. One of these uses multiple steps in order to ultimately produce query results in a data sheet view.

What one of the users does at this point is to filter on certain columns based on whatever further criteria he may have. Once he is complete with the filter set of rows he would like to edit one of the fields for multiple records.

Now we already have a process in place where the source records can be altered but not directly from the dataset I described. He would normally do a copy and paste of the pertinent fields from Access into Excel, make his changes and save the spreadsheet. He can then import those records into the Access database. We have a macro set up to do the import from the file automatically for him.

My question is can I programmatically have the filtered results copied and sent to a temp table? Once in a table I could have the user make his changes, save it and possibly run another macro that would update the source data. If I can simply do copy of filtered results to a table in Access I can handle the rest. It would also skip the step with Excel which is preferable.

I tried several approaches, but since this is a plain old query any copying of it seems to copy the query object(thus the unfiltered results), not the results. This also doesn't put the results in a place where they are editable.

I even attempted a sendkeys macro but I really don't want to go down that road. I really feel like this is something simple, I just do not know all the in's and out's of VBA syntax.

Thanks for any help you may be able to provide,

Heywood

Hi,

I am trying to use VBA to open up an access database, enter values into a record from a series of defined names into a temporary table, then eventually run an append/update query to copy this record into the main table.

At the moment I am struggling with the first step, which is giving me the error:


runtime error 214721783(80040e4d) Authentication failed

This is the code:

	Code:
	Sub SelectDB()
'Select Database file location
PEMdb  = Application.GetOpenFilename(FileFilter:="Access Files (*.mdb),  *.mdb", Title:="Please select the location of the
Personnel &  Equipment Management Database")
If PEMdb = False Then
' They pressed Cancel
MsgBox "The operation was cancelled", vbInformation, "Import Cancelled"

Exit Sub
Else
Workbooks.Open Filename:=PEMdb
End If
End Sub

Sub Validate_Import_1()
'Validate Data before importing

End Sub


Sub Import_Stage_1()
'Export data from excel into Access DB

Call SelectDB

Dim cn As ADODB.Connection
Dim cmPerDet As ADODB.Command


Set cn = New ADODB.Connection
Set cmPerDet = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & PEMdb & "; Persist Security Info=False"
.Open
End With


With cmPerDet
.CommandText  = "Insert into tbl_TEMP_Personnel_Details(Employee ID, First Name, Last  Name) Values ('" +
Range("Employee_ID").Text + "','" +  Range("First_Name").Text + "','" + Range("Last_Name").Text + "')"
.ActiveConnection = cn

End With


End Sub


The database will eventually be split with the back-end on a network drive, but at the moment I am getting this error even when it's located on my own desktop.

Any suggestions? It's the .Open line that is highlighted by the debugger. I have tried both changing selectDB() from a sub to a function and removing it completely (replacing PEMdb with a path & filename) but to no avail...

Thanks,

Duncan

Hi all

I think I have found a solution now but I want to still ask a couple of questions regarding this as I've spent hours on it. and want to understand a bit more. Sorry, its a bit lengthy... (My solution is right at the bottom)

Basically, I want to import text files into an Access table and also export data from a table to a text file.

I first used the transferText method, which does the job fine when using the spec name specified in the advanced options when importing the table for the first time manually.

Here is the code... Which works fine!

	Code:
	Sub ImportData()
  'This uses the 'Saved Specs' when the table is first imported manually
  DoCmd.TransferText acImportFixed, "MySpecs", "MyTable", "C:myFile.txt", fasle
End Sub
Sub ExportData()
  'This uses the same 'Saved Specs' that were used when creating the table
  DoCmd.TransferText acExportFixed, "MySpecs", "MyTable", "C:myFile.txt", fasle
End Sub

However I wanted a vba solution using schema.ini file so that I could easily change bits if necessary.

I found this work around due to not being able to specifiy custom ini files in the TransferText method. I just had to modify to work whether the table existed or not.

Here is the code... Again, this works (Sort of)

	Code:
	Sub ImportSchemaTable()
 
  ' This uses my own schema.ini file.  If the table doesn't exist, it creates it.
  ' If the table does exist then the data is appended
  Dim Db As DAO.Database
  Dim bExists As Boolean
  Set Db = CurrentDb()
 
  On Error Resume Next
  bExists = IsObject(Db.TableDefs("MyTable"))
  If bExists Then
    Db.Execute _
      "INSERT INTO MyTable SELECT * FROM " & _
      "[Text;FMT=Fixed;HDE=Yes;DATABASE=C:myFile.txt;].[myText#txt];", dbFailOnError
  Else
    Db.Execute _
      "SELECT * INTO MyTable FROM " & _
      "[Text;FMT=Fixed;HDE=Yes;DATABASE=C:myFile.txt;].[myText#txt];", dbFailOnError
  End If
 
  Db.TableDefs.Refresh
 
  Set Db = Nothing
End Sub

The problem with this is that that table doesn't show up until I close Access and reopen.

Also, if I amend the ini file it will not change the how it appends data to the table unless the table is deleted first and then created again using the amended ini file. e.g. if the first colum has a fixed width of 10 the first time it imports it will be 10, but no matter what I amend it to it will alway be 10 unless the table is deleted.. Hope this makes sense.

Questions
1, Is it possible to use a custom ini file with the transfertext method? Have Microsoft fixed the issue?

2, On my second method, can I get the table to appear without having to re-open Access?

3, Why does the import data fixed width not after the ini is amended?

My Solution

After a long time trying to get the above solutions, I found out that you can amend the specs created as follows. And this is alot quicker than having to do the manual import again and savespecs again.

In Access 2007
In Access Options > Navigation Options, click Show System Objects.

In Access 2003
In Tools > Options > View, click System Objects.

This lets you modify the column names and widths in MSysIMEXSpecs and MSysIMEXColumns.

Appreciate any help/adivce or tips on any of this.

Thanks

Hi

I have one front end application which in MS access and few backend databases (MS Access). I'm able to export the whole table from one database to another database using below code. But my issue is I want export only selected columns only and based on condition (e.g. Date > so and so).


	Code:
	Private Sub ImportFromAccess(sDatabaseID As String, sSourceTradeBookTbl As String, sDestTradeBookTbl As String)
    Dim objApp As Object
    Dim strPnPDatabaseName As String
    Dim strPnPDatabasePassword As String
    Dim strSourceDatabasePathNName As String 'Source Database Path and Name, from where data need to imported
    Dim strDestDatabasePathNName As String 'Destination Database Path and Name, where data need to exported
    strSourceDatabasePathNName = ExtractDatabaseDetails.GetDatabasePath(sDatabaseID) & "" &
ExtractDatabaseDetails.GetDatabaseName(sDatabaseID)
    strDestDatabasePathNName = ExtractDatabaseDetails.GetDatabasePath(gsPnPDatabaseID) & "" &
ExtractDatabaseDetails.GetDatabaseName(gsPnPDatabaseID)
'    strPnPDatabasePassword = GetDatabasePassword(gsPnPDatabaseID)
    Set objApp = New Access.Application
    
    'Open current database to export the table from this opened database to destination database
    objApp.OpenCurrentDatabase strSourceDatabasePathNName, False
    
    'Export the needed tables and queries
    'Parameters
    'strNNADatabasePathNName - Source Database name with full path
    'strNNASourceTableName - Table Name from Source Database
    'strNNADestTableName - Table Name from Destination Database
    'False - You're importing all the data as well, True would just import the structure
    objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _
                                        "Microsoft Access", _
                                        strDestDatabasePathNName, _
                                        Access.AcObjectType.acTable, "NNA data", "tbl_NNA"
End Sub

My source and destination both databases are password protected (currently I have removed the password for testing).

I don't want to use LOOP means SELECT data from the source database and use loop and insert one by one record in target database cause there are 150K records and this will be very slow. Is there better solution to use SELECT ColA, ColB, ColJ, ColP FROM XXXXTable (Source Database password protected) and INSERT INTO Destination Database (Password Protected). If possible I can use TransferDatabase method but I'm not able to figure out how to export only selected columns

I don't want to use link table as well.

Is there anyway to disable the access application as I'm using OpenCurrentDatabase so it's flashing up the database. I know if disable the security warning manually once in access then it will not make the application "visible" but this need to be distributed to users and I can't disable this in each PC.

Please help. Thanks in advance.

Dear All,

I have a list of product codes in Excel (column A) and I need for each one of them to connect to an Access database and retrieve the corresponding sales value and have it in column B.

I have tried exporting the table from Access to Excel but then the vlookup takes a long time. The opposite, importing the product codes in Access, is even harder because I have 50 worksheets where the field name is not always the same and sometimes the lookup value is in different columns.

I was looking for a query on Access that would have as criteria-input the cells of Excel where the product codes are.

TIA,

cosalex

I have a chart to build using data from my Access Database. Most of the data is coming from one table but I am having a hard time finding a way to build a chart in Access. So my thought was to import data from the Access DB and send it to Excel that way I could format the data into columns and create a bar graph.

I am not very familair with technique I know it can be done but I am looking for a how to guide/tutorial that could possibly explain it to me. Or if someone has knowledge of how this can be achieved. I would appreciate any advice you can give. I assumed the easiest way to accomplish this task would be to use VBA to make the connections.

My other question would be can I export the graph I make in Excel and bring it back in Access or would it be better to just have a DoCmd to open the Excel Chart in Excel?

Thanks in Advance.

I have recently taken over the duty of maintaining an Access '03 database linked with VB. The program employs a series of recordset objects and connection objects associated with type ADODB. Thing is, I have absolutely no idea what the distinction is between DAO, ADO, and ADODB. Anyone have a clue? Probably - I'd appreciate the help.

What I would like to do is export to Excel a recordset object from Access, to make a file that I can import to MapPoint. But, do not concern yourself with the latter objective; all I care about is automating the process of transferring data from Access to Excel.

What is the formal definition of a recordset? What does a connection do? I have an Access book right in front of me and it's terrible. 1300 pages of nothing. What a waste of money. If someone is able to explain the function of a recordset and connection in simple terms I would be so thankful. And thanks for reading.

Hi, this code imports contacts from an accessquery into an exchange public folder for people to use - i import the unique contact ID from the access dbase into the job title field - i want to add code that will check the job title field against the ID in the query and if it is already there i do not want it to create a duplicate contact as it presently does, but i do want it to overwrite the outlook contact with the latest details from the query. any new contacts should just be imported as normal - does anyone know the code required?

thanks.

Private Sub outlook_Click()

Dim rsdbase As Database
Dim rstemp As Recordset
Dim olns As NameSpace
Dim cf As MAPIFolder
Dim c As ContactItem
Dim ol As New outlook.Application
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(18).Folders.Item("wcc")
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("qryemailcontacts")
With rstemp
.MoveFirst
Do While Not .EOF
Set c = ol.CreateItem(olContactItem)
c.MessageClass = "IPM.Contact"
Set c = cf.Items.Add
If ![contactID] "" Then c.jobtitle = ![contactID]
If ![companyname] "" Then c.companyname = ![companyname]
If ![firstname] "" Then c.firstname = ![firstname]
If ![surname] "" Then c.LastName = ![surname]
If ![email] "" Then c.Email1Address = ![email]
c.save
.MoveNext
Loop
End With
Set ol = Nothing
Set olns = Nothing
MsgBox "All Contacts Successfully Exported to Outlook!"
End Sub

Hi all,

I need to export data from a query to a text file.
Hope you can help me with a simple way of doing it.
Im going to import data from a access database to a new table, from that table i need to export data to a fixed width, and name the file with some of the data.
Some of the fields must be filled with "0" or spaces with the export data aligned to right. All record in database will have 125 chars in each exported text line.
The file must be named like some of the fields like date and some codes that are on the same table.
I want to have a button that exports to the txt file.
Can someone help me with the basics please.

Thanks in advance
Luis

Hello,

I'm working on an access database (Access 2003) and exporting queries to Excel (Excel 2002). The intention is to get the Access VBA code to format the excel file for me so that all the exports will all look the same, and allow for easy re-importing after other parties (users) have added data to the excel file.

The exporting is working OK, some formatting is working OK as well, however I have 2 major issues that are not working for me, however someone is bound to have come across this before:
How to hide collumns in Excel (from Access VBA) specifying the collumn number (not the letter(s)). How to specify ranges in an excel worksheet (for formatting) using access VBA using the cell numbers (i.e. cell(1,2) , cell (1,4) which would be equivalent to B11). The idea of this function is to export a recordset (which can vary in size depending on the query run) and that it only formats the area in which the results are displayed.

As the recordset changes in size each time, I want to be able to automatically change the area(range) that is formatted. Retrieving the size of the recordset is not a problem, but using this result to change the range of cells that need reformatting is.

In addition there are some collumns that I want to hide to enable easier re-importing of the data later. (want to hide the data primary key)

The code that I am using is listed below. I have tried some things (as you can see from the code listed) and I've listed which don't work.

Looking forward to someone's bright idea on this one!

Thanks,

Arjanvb

'-------------------------------------------------------------------------------------------------------------------------

Public Function ExportXLS() As String

' Base methodology copied from: http://july-code.blogspot.com/2007/1...-to-excel.html

Dim oApp As Excel.Application 'in VBA : Tools : References " MS Excel reference library is required
Dim oWB As Excel.Workbook
Dim i As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SQLstring As String
Dim CHANIDnum As Integer
Dim TempCounter As Integer
Dim RowStart As Integer 'to indicate which row Excel starts with putting data in
Dim CollumnStart As Integer 'to indicate which collumn Excel starts with putting data in
Dim NumRows As Integer
Dim TempString As String
Dim NumCollumns As Integer
Dim oSheet As Excel.Worksheet 'from excel example
Dim oRange As Excel.Range 'from excel example

'input channel ID number & other items for conversion to function later
CHANIDnum = 60

SQLstring = "SELECT tbl_QUES.CHANQNUM, tbl_QUES.QUESTEXT, tbl_QUES.ANSWTEXT, tbl_QUES.DATEQUES_SENT, tbl_QUES.QCURR, tbl_QUES.LOGNUMQ, tbl_QUES.QUESID, tbl_QUES.CHANID, tbl_QUES.QANSW "
SQLstring = SQLstring & "FROM tbl_QUES "
SQLstring = SQLstring & "WHERE (((tbl_QUES.DATEQUES_SENT) Is Null) AND ((tbl_QUES.QCURR)=Yes) AND ((tbl_QUES.CHANID)=" & CHANIDnum & ") AND ((tbl_QUES.QANSW)=False)) "
SQLstring = SQLstring & "ORDER BY tbl_QUES.CHANQNUM"
'end input of channel ID

'Create an instance of Excel and add a new blank workbook
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLstring, dbOpenSnapshot)

Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add

'Following deletes excess sheets in workbook to leave only one sheet
If oWB.Sheets.Count > 1 Then
Do While oWB.Sheets.Count > 1
oWB.Sheets(oWB.Sheets.Count).Delete
Loop
End If
'End deletion of extra sheets

'To hide collumn
'oWB.Sheets(1).Collumns("B:B").EntireColumn.Hidden = True ' does not work
'oWB.Sheets(1).Collumns(10).EntireColumn.Hidden = True ' does not work
'oWB.Sheets(1).Collumns(1).Hide ' Does not work
'oWB.Sheets(1).Collumns(1).Visible = False ' Does not work


'StartCell filling with Q at row 8
RowStart = 8
CollumnStart = 1 'offset for data on spreadsheet

'Check record length & set this for formatting purposes
rst.MoveFirst
rst.MoveLast
NumRows = rst.RecordCount
rst.MoveFirst
'end check record length for formatting

'Check record width & set this for formatting purposes
NumCollumns = rst.Fields.Count

'Add the field names as column headers (optional)
For i = 0 To rst.Fields.Count - 1
oWB.Sheets(1).Cells(RowStart, i + 1 + CollumnStart).Value = rst.Fields(i).Name 'Reads name in from Database, 2 is for begin in second collumn (need to change if QUESIDNUM is added)
Next

TempString = RowStart & ":" & RowStart ' make string to define range definition based on RowStart

oWB.Sheets(1).Range(TempString).Font.Bold = True
oWB.Sheets(1).Cells(RowStart + 1, 1 + CollumnStart).CopyFromRecordset rst 'Copies in recordset - offset 1 for header data
oWB.Sheets(1).Name = "QuestionList" 'Set Worksheet Name

Set oSheet = oWB.ActiveSheet
Set oRng = oSheet.Range("B1", "E1") 'range is set from A1-> D1

oSheet.Range("B1").ColumnWidth = 15 'works
oSheet.Range("C1").ColumnWidth = 50 'works
oSheet.Range("D1").ColumnWidth = 50 'works

'Use with selection method - select question area

'Dim SetLineStyle As String
'SetLineStyle = xlContinuous

'Need better way (with numbers) to select ranges within EXCEL from Access
TempString = "B" & RowStart & ":" & "G" & (NumRows + RowStart) ' still to implement collumn width setting of range
'End better range definition

With oWB.Sheets(1).Range(TempString)
.Borders.LineStyle = xlThick
.Borders(xlRight).LineStyle = xlContinuous
.Borders(xlLeft).LineStyle = xlContinuous
.Borders(xlTop).LineStyle = xlContinuous
.Borders(xlBottom).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.VerticalAlignment = xlTop
.WrapText = True
End With

'Set oResizeRange = oSheet.Range(.Cells(1, RowStart), .Cells(3, RowStart + NumRows)) ' Does NOT work
'Set oResizeRange = oSheet.Range(Cell(1, 1), Cell(5, 6)) 'Does NOT work
'Set oResizeRange = oSheet.Range("A8:C30") 'Does work

'Clean up ADO Objects
rst.Close
Set rst = Nothing

'Create a folder if not exist
Dim strFilePath As String
Dim strFolder As String
strFolder = "C:Temp"
strFilePath = strFolder & "Rpt_" & Format(Now(), "yyyymmdd_HHmmss") & ".xls"

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(strFolder) Then
'Create the file
FileSystem.MkDir (strFolder)
End If

'Clean up Excel Objects
oWB.Close SaveChanges:=True, FileName:=strFilePath
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing

'Open the file after export to excel
Shell "EXCEL.EXE """ & strFilePath & "", vbNormalFocus

End Function

Using bookmarks, I am exporting some data from by database to a Word document, which is then attached to an email.

The receipt of the email is suppose to respond the information contained in the document using text fields and five comboboxes and email back so we can automatically import the response back into our database. The problem is that in order to get the comboboxes to work you have to protect the word document first. Once protected, Word will not permit me to automatically fill in the data fields.

Is there a way to have Access enable document protection after it has exported the data to the word file?

Importing a table or a form or report from one database to another is quiet simple in MS Access 2007.
Exporting or importing a saved import- or export specification -e.g. a table that must be exported to a csv file in a specific folder from one database to another seems to be a whole other story.
Until now i couldn't find out the location where Access stores the saved specification, so the transfer of this saved specification to another database seems to be impossible.
Can someone tell if this is possible and if so how to do it?

Hi, i have two identical databases, located in two seperate countries.

I would like to update one database with information from the other (and vice versa). Currently these databases are accessed on local area networks. Security is a big issue here. Is there a simple method of being able to import/export table information to keep them both up-to-date with one another?

All thoughts most welcome.

Thankyou.

Hi all,

I have an access database to manage when people need to retake a qualification. I would like find a way to integrate these into an outlook calendar as individual entries.

I have found one way by exporting to a csv file and then importing that from outlook. Is there a way i can automate this?

Regards

Sean

Dear all,

I have a packaged access 2007 solution (using access developer's extensions) and i need to add to my users the backup/restore database feature and the import/export feature.

kindly note that this is available in the access 2007 normal version ( not packaged).

thank you,


Not finding an answer? Try a Google search.