Modules & VBA :: Importing A Table Without Knowing Its Name?
Aug 13, 2013
how to import a table using VBA like so:
Code:
Private Sub Command0_Click()
Dim dr As String
dr = Dir("F:SomeFolder*.mdb", vbDirectory)
DoCmd.TransferDatabase acImport, "Microsoft Access", "F:SomeFolder" & dr, acTable, "Table", "Table2"
End Sub
This was just a test to see if I could get an import to work. My problem is that I have a lot of .mdb/.accdb files with similar tables that I need to add into one larger database and these similar tables could have different names in different folders. For example, a file named db1.mdb has a table named tbl. Another file named db2.mdb has a table with all of the same fields, but its called tabX. Furthermore, db2.mdb could even contain a second table that I need to import.
My question is:
Is there a way to simply import all the tables from an access database without knowing any table names using VBA?
We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:
Code: // Column 1 Data/StuffIDontCareAbout/ StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data StuffIDontCareAbout/StuffIDontCareAbout/ Column 3 Data
[Code] ....
...and so on and so forth anywhere between 50-100 more times
The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.
Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash). Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line
Here's a quick example of what I'm talking about:
Code: // 37 NOS001/DCO/ TaskPer/TBD//310001ZAUG2014-292359ZSEP2014 GenText/Remarks/ (U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.
[Code] ....
So after it's parsed, my table would have the following new rows:
Code: Column 1 | Column 2 | Column 3 37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain 582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task
Function SyncSuppliers() On Error GoTo errhandle Filename = DLookup("SupplierPath", "Setup", "SetupActive = True") If Filename = "" Then Exit Function End If Set xlapp = CreateObject("Excel.Application")
[code]....
The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.
I am importing csv files into tables in a batch routine and I get the following error messages. The error number is always 2391.
Field 'F1' Doesn't Exist in Destination Table Field 'F4' Doesn't Exist in Destination Table Field 'F36' Doesn't Exist in Destination Table
I understand the first one and can find references to this on the web but the F4 and F36 escape me.
All fields required do actually exist in the table so the real problem is elsewhere.
P.S. I now think that it may be that there are embedded commas in one or more of the text fields and that the number following the 'F' indicates the position of the field in the table. I will check in the morning or create the csv files using commas and quotes.
I am running this code to import an Excel sheet into a table in my database. This works fine if I set [HasFieldNames] to false. When [HasFieldNames] is set to true, I get the correct field names in the table, but don't get any data from the sheet.
what is the best way to import the data from the XML file into an access database table. The database I am working with has one large main table where all of the main record data is stored. There is a somewhat complex string of queries and reports based off this table that I am concerned about preserving. The problem is that the XML file is not structured in the same way the table is. The headings are named different, aren't in the same order, etc. I cannot use the import method and simply append it to the main table.After much searching around I have found two options:
1) Use the built in XML import method that access provides to create a secondary table. Then find a way to take data from individual fields in the second table and map and insert it into a new record in the main table.I already have the import part of this option working. The only part I can't seem to understand is how to take data from the second table and get it into a new record in the main table under the correct headings
2) Read the data from the XML file all at once and then map and insert it into the main table.I have not attempted this yet. I was having a hard time understanding how to retrieve the data from the XML file in the first place.
So.. which would be better/easiest to automate (most likely via button click on a form)? I only have a small understanding of VBA and even less understanding of anything XML.
I currently use the following to import a csv file into a staging table and then append and save the data to a table - however it doesnt use the headings from the csv file and creates an error table and a blank record where the headings should be apart from one column where it copies the name.
Would it be possible to use the headings as field names or just omit them completely and use the standard f1 f2 f3 etc access generates in the tmp table?
Code: With Application.FileDialog(msoFileDialogFilePicker) .Title = "Select the CSV file to import" .AllowMultiSelect = False .Filters.Clear .Filters.Add "CSV Files", "*.csv", 1 .Filters.Add "All Files", "*.*", 2
Code beneath worked perfectly with Access 2003.Now we switched to Access 2010 and it generates a table where data isn't put into my 77 fields like before, but seperated into 1 field, separated by some ;;;.I am not good with programming.
Dim rst_data As Recordset Dim oldname As String, newname As String DoCmd.DeleteObject acTable, "TBL_import_TPXP_Radi_Evvd" DoCmd.TransferText acImportDelim, , "TBL_import_TPXP_Radi_Evvd", "N:APPLSHAREPRDQSIGMKTDISTPWBUSPB1815RADIEV IMPACT.CSV", False, ""
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
I wish to extract data from my access database...namely all packages we are taking to the post office for dispatch that day. The post office form looks like this...
I need to extract the name the address & postal service used (this data is all contained in our access database. I envisage using a 'flag' to trap those orders that haven't been posted yet (in other words export only those database entries that have not been flagged as posted, then afterwards update the database to change the flag to 'posted')
My dilemma is that I've not really used reports much (I'm assuming that reports is the correct way to go here)...how do you format the output of a report, when you don't know how many lines are going result (some days might see 10 order, other days 20 ...and so on)...
I have a database with existing tables.I now want to add a whole bunch of records into my access tables.A lot of the data is repeated, and lends itself to my doing it in excel with it's better copy and paste abilities, and it's ability to easily increment alphanumeric fields.I can't seem to succeed in append importing to the bottom of my access table from my excel spreadsheet. All columns are the same.Can this be done, and if so how please?I'm on XP Pro with Office 2003 Pro.I'm also very much a newbie in Access.
I have to import a file from an external website into a table (tableA) that I have established. The issue is that I don't need all that data, just specific ones (the website does not allow me to select the specific data to export). I created another table (tableB) to capture the data that I require for my report.
How do I take raw data from tableA to append to tableB the required fields that I want?
I have 200 excel files in folder C:UsersLburchDocumentsHistoric DataTedan Data in Excel Worksheets - CopyAAA - Copy.The files only have one worksheet and the column format are the same. Worsheet name is different for every file though.Headings in first row.write a macro to import into 1 Access table so I don't have to do it manually.
I have a pretty basic TransferSpreadsheet module that works well except that if the Zip code has the four digit extension, it's not importing.
Option Compare Database
Dim myCheck Function MeridianLinkFileImport() DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_MeridianLinkFileImport", "R:DEPT-BRCONSUMER LENDINGMarketing Campaigns2014 Auto Loan PrescreenReport from LoansPQ.xls", True, "Sheet1!A3:AO50000" End Function
If there is no extention, it is importing fine... 85086 works fine but 85018-4710 doesn't import.
I have table1 in my (Access 2010) database that has exising records. I have another table2 that after I run a query, it first deletes the data in table 2, then imports new records into that table. I run the import into table 2 on a semi regular basis but have yet to copy those records into table 1.
With that said, using either a query or VB, how do I copy all the (new) records from table 2 into table 1 without altering any records that already exist in table1?
BTW, Table 1 has a main form with a subform within it
Is there a way of importing MS Excel Spreadsheets but starting from row 8. I am now getting lots of spreadsheets but i only need data from row 8. I have to manually delete the rows and save the spreadsheet then import. Some days i can have more then 1.
I am using below code to import various Excel spreadsheets into Access. However every now and again below code goes into error, usually because there is no "toimport" range in the Excel file.
So I would like to use On Resume Next, so it can import all other files. But in order to do it, I need also to be able to somehow capture information about all files that went into error, so I can fix them.
I would like to create like an "error log" and I would like Access to update it with information about all the files that were not uploaded + error message + date/time.
Ideally I would like it to be in Excel file. I tried to use DoCmd.TransferSpreadsheet but it does not do what I need
Code: Function import() Dim strFile As String DoCmd.SetWarnings False ' Set file directory for files to be imported strPath = "C: est"
I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).
I am using a sub that feeds a string to another public sub as follows:
Code: Private Sub cmdImport_Click() Dim strFolder As String 'source folder path for import Dim strFile As String 'individual file path strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti") If Len(strFolder) <= 1 Then Exit Sub
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code: Dim strFile As String DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM Data;" ' Set file directory for files to be imported strPath = "C: est"
I have a code that allows the user to select the file that they want to import, however it automatically imports the first worksheet into a specific table. Is it possible to change the code to make it import the first worksheet (or with a specific name) into one table and another worksheet into another table?
Code: Dim dlg As FileDialog Set dlg = Application.FileDialog(msoFileDialogFilePicker) With dlg .Title = "Select the Excel file to import" .AllowMultiSelect = False .Filters.Clear
I have a linked csv table into access, and have a make table query which runs off this. When i first build and run the query it runs fine, but when i run it again I get the following problem "numeric field overflow" I have checked all the data types, they match fine.....
The thing that i cannot understand is when I rebuild the query from scratch, it works fine for the first couple of times, then stops.
I have a database called "polls" with a table called evote which is distributed to the public to use. What i'd like to do is create another database called import with a form that is used mainly to browse for a file and import it to the "polls" database. It should overwrite the table evote with new information.
Are there any examples of how i should get started? I tried search but couldn't come up with anything
I am trying to create a txt file to import into our accounting software. I get the file (its blank), but it fails on the WriteLine and i get the run time error. I have a command button on a form that the user will click to export the file.
Private Sub cmdExport_Click() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim strPath As String Dim strPathGB As String
