Visual Basic :: Automatisation Of Import .txt In Access

Import A CSV Into Access From VB6
Hi,

Here is the scenario, I'm writing an application in VB that pulls data either out of an Access or SQL db, run's it through some software to varify the addresses and then reimports it and does some resulting.

The problem is i can't find a way i'm happy with for getting the data back into Access, with SQL its not a problem i just use a DTS with some global variables.

The only way i can see to do it is to open both the text file and the Access db, and loop through the text file adding in records to the Access table, which is very slow.

The only other possible way i can think to do it is to use a SQL dts with a text file source and an Access Detination, but i'm not keen on that either, bit round the house's + the users may not have access to a sql server.

Anyone got a better way to do it?

Thanks
Bob.

Access 2k Import
Hey y'all,

I have just written a new import routine which uses the following line:

Code:
DoCmd.TransferText acImportDelim, "MPEImport", "HOLDING_DATA_MPE", strMPEInputFolderPath & "" & strNewInputFileName

The import spec MPEImport has the same fields as the table HOLDING_DATA_MPE bar 1 (which is used for date formatting).

I have tried to manually import to check that the output file to be imported (strNewInputFileName) contains the correct fields in the correct order. The fields are all in the correct places but I get an error telling me that the file may not exist (altho it definatly does coz I had to find the file in an FSO to import it and I could see the field values in the wizard).

Deciding that this could be a bug with the manual import, I proceeded to test the code import. I get no error but some of the fields are missing so the remaining fields have all been pushed over to the left.

Does anyone know what could be wrong here?

The file needs to be changed to txt format (from a .bill extension) , which is done in code but can be done manually if you want to try a manual import. It can be read in notepad.

I have attached the .bill file (also sheet1 on .xls), the import spec MPEImport (Sheet2 on .xls) and the resulting HOLDING_DATA_MPE table content (sheet3 on .xls)

Import / Query In Access Via VB
Hiho,

i have the following problem which includes these three componets:

- vb application
- datafile (*.txt)
- access database

now i want a command button in VB, which when it gets clicked imports the *.txt in the access database an runs a view queries.

How can this be realisied, can anybody give me some suggestions or a vb code example?

thx & c ya

Salva

Import Excel Into Access.
I know there are a number of examples of this already but I can't seem to get them working. This is my code and I get an error (80004005) 'The database has been placed in a state by user...'

I assume I can't run this code from the mdb I am trying to import into. What I want to do is have a button on a form that will load an excel file to a table. So - from excel to an open access db. Does anyone have time to look at this?

Code:
Private Sub cmd_importExcel2_Click()
Dim Cn As ADODB.Connection

Set Cn = New ADODB.Connection

'lets connect to the workbook first, I tested this, it works for me
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:New ProjectsImpact Tool est.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"

<b>'Here is the problem - can I just point to the current open db?</b>
' Append data from Sheet1 of workbook to Table1 of mydb.mdb:
Cn.Execute "INSERT INTO [Table1] IN 'D:New ProjectsImpact Tool est.mdb' SELECT * FROM [Sheet1$]"

'Cn.Close
Set Cn = Nothing
End Sub

After this - does anyone know of good links that show how to read through a record set? What I would like to do is load the excel file as a RS before I insert it - and then check the first two fields to see is they are valid (if the city is in the right province etc.)?

Excel Import To Access
I'm running this code in a window script. (extention .vbs)

set oAcc = CreateObject("Access.Application")
oAcc.Visible = True
oAcc.OpenCurrentDatabase "C:Shipping DatabaseShippingDatabase.mdb"
oAcc.DoCMD.TransferSpreadsheet 0,8,"Freight Tracking Database","C:InetpubwwwrootCopy of SHIPPING.xls",0
oAcc.UserControl = True

I need something a little more complicated. My Field Names are not the same between Excel and Access so the TransferSpreadsheet doesn't work. I would like to run an SQL INSERT Statement to transfer everything over and then I can use aliases and other fun things. I know another way to do this in asp but you have to have a named range which isn't possible in my situation. I'm not sure where to go from here. I don't have visual studio so everything I do needs to be done from windows scripting host or asp.

Basically:
Is there anyway to execute a SQL statement in the code above that will affect the whole spreadsheet without needing a named range?

Import From Word To Access
Is it possible to import data from a table in a word document into an access table?

The table in Word is a standard table and the first cell contains a bookmark.

Any suggestions?

tnx

Access Import From Excel Help
Hello all,

Oh, here's the deal I have information in Microsoft Excel, which I need to process and append to the access database.

So I first import the data from excel to access on a temporary table using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", strRet, False, "Sheet1!A2:N65536"

Having done that now i need to cycle through each record in the temporary table and send it into the production database with those who satisfy my SQL statement ...

I need all this to be done programmatically... HELP

Import OLE Object Into Access Via VB
Hello All, I creted some code that automatically exports a single message from Outlook. However, problem occurs when I want to import that message into an Access 2002 database. Does anyone know how to import an OLE into an Access database via VB?

I have a table called tblTemp with two fields; PurchaseID (auto number) & ApprovalDocument (ole object).

Thanks for the help.

Import A Form From Access To VB6
In an effort to port over an older MS Access project to Visual Basic, I am wondering if it's possible to import the forms. I can pull the code over easy enough, but I can't find a way to get the actual form. Is this even possible?

Need Help With Access VBA And Text Import
Thanks!

I have been moderately successful after scouring the web for suggestions. I am now having problems importing the text from a file into an array. Maybe an array is not the best solution. Aay ideas would be greatly appreciated.

Here is a sample of the text file: I want to be able to read in each line into a a field in the database. Each trip between the *========= lines varies in number of lines.

Thanks again,

Jim

*===================================================================== ========*
J2291 Check-In 15:35 Check-out 16:10 MAY2005 5-Day
Flt Dep Local Arr Local Turn Rest Block Duty +---------------------+
91 JFK 16:35 OAK 19:45 *10:35 06:10 07:25 |S M T W T F S |
Airport Courtyard (510-568-7600) |=====================|
474 OAK 07:20 BOS 16:00 *13:30 05:40 06:40 |-- -- -- -- -- -- -- |
HOLIDAY INN (617-569-5250) |-- -- -- -- -- -- -- |
481 BOS 06:30 LGB 09:45 *23:25 06:15 07:15 |15 -- -- -- -- -- -- |
Marriot Airport Long Beach (562-425-5210) |-- -- -- -- -- -- -- |
307 LGB 10:10 IAD 18:05 *13:10 04:55 05:55 |-- -- -- |
Holiday Inn Select (703-815-6060) +---------------------+
301 IAD 08:15 FLL 10:45 02:25*
40 FLL 13:10 JFK 15:55 05:15 08:40

Credit 28:15 TAFB 96:35
*===================================================================== ========*
J2292 Check-In 17:15 Check-out 09:10 MAY2005 5-Day
Flt Dep Local Arr Local Turn Rest Block Duty +---------------------+
107 JFK 18:15 OAK 21:40 *11:25 06:25 07:40 |S M T W T F S |
Airport Courtyard (510-568-7600) |=====================|
472 OAK 10:05 BOS 18:45 *11:45 05:40 06:40 |-- -- -- -- -- -- -- |
HOLIDAY INN (617-569-5250) |-- -- -- -- -- -- 14 |
441 BOS 07:30 TPA 10:45 00:50 |-- -- -- -- -- -- -- |
444 TPA 11:35 BOS 14:30 *22:40 06:10 08:00 |-- -- -- -- -- -- -- |
Omni Parker House (617-227-8600) |-- -- -- |
435 BOS 14:10 MCO 17:10 *12:20 03:00 04:00 +---------------------+
Country Inn & Suites (407-856-8896)
52 MCO 06:30 JFK 08:55 02:25 03:25

Credit 23:40 TAFB 87:55

Automated Import (ACCESS)
Hello all,

Here's another conundrum for you guys. I'm trying to create a facility whereby the user would be able to choose various types of files to import (Excel, Text etc...) and their location and import them into a designated table (created on the fly, but that's not important right now)

My problem is that the format of the files are different every time (coming from different clients), so i don't know how i could duplicate the wizard that analyses the data and puts it in the right fields i.e. delimited or fixed width option, seperated by comma and so on...

Now, Excel is easy (as long as the input table has F1,F2...Fn as field names), but how do i do the same with a TXT file, especially when it comes to Fixed Width and a DOS method of string saving (try any export from access to see that).

Any help here would be hot.

Thanks,

Dor

p.s. I supply the code i tried with readline, putting it into an array for analysis, but that's where i come stumped.

Code:

'Connect to external database and delete the array table so it can be recreated
Set dbClientDB = OpenDatabase(strDBPath, True, False)
For Each tblArrayTable In dbClientDB.TableDefs
If tblArrayTable.Name = "tblArrayTable" Then
dbClientDB.TableDefs.Delete tblArrayTable.Name
dbClientDB.TableDefs.Refresh
Exit For
End If
Next tblArrayTable
'Create the array table with 250 fields for examination by the user
Set tblArrayTable = dbClientDB.CreateTableDef("tblArrayTable")
For lngCounter = 1 To 250
Set fld = tblArrayTable.CreateField("F" & lngCounter, dbText, 250)
tblArrayTable.Fields.Append fld
Next lngCounter
dbClientDB.TableDefs.Append tblArrayTable

'Delete the link to the array table if exists in the database and relink it (works on whether it existed b4 or requires it anew)
For Each tblArrayTable In CurrentDb.TableDefs
If tblArrayTable.Name = "tblArrayTable" Then
DoCmd.DeleteObject acTable, tblArrayTable.Name
CurrentDb.TableDefs.Refresh
Exit For
End If
Next tblArrayTable
DoCmd.TransferDatabase acLink, "Microsoft Access", strDBPath, acTable, "tblArrayTable", "tblArrayTable"

'Determine client file format and import 10 lines from the file to put into the array table for examination by user
Select Case fraFileType
Case 1 'Text
Set varScriptingAgent = CreateObject("Scripting.FileSystemObject")
Set varImportTextFile = varScriptingAgent.OpenTextFile(fldImportFileLocation, 1)
For lngCounter = 1 To 10
If varImportTextFile.AtEndOfStream Then Exit For
strImportLineArray(lngCounter) = varImportTextFile.readline
Next lngCounter

'This is where i come undone. How can I analyse the data in the array to determine what to put in which field???

Case 2 'Excel

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblArrayTable", fldImportFileLocation, False
'Various code to change the field names as requested if first line contains field names

End Select

Import A Access Report Into VB
Is it possible to Import a Report Inside a Access Database into Vb?

Import Text To ACCESS
Hi everyone..
I am actually doing the program in ACCESS using DoCmd. Transfertext. I set up the specification file, and manually it works and import the file on the table. But to automate it.. I have something like..
Dim strFile As String
Dim myPath As String

MsgBox CurDir
myPath = CurDir
strFile = myPath & "sr14"
strFile = "" & strFile & ""
DoCmd.TransferText acImportFixed, "sr14-import", "SR0014short", strFile
MsgBox "SRR014 raw data upload was successfull", vbOKOnly

Now the problem is I get a runtime error:Runtime error"3051" MsJet database engine canot open the file "sr14.txt"
It is already open exclusively by another user, or you need permission to view its data..

I don't understand, the file is not at all open, and also the file cannot be opened by another user because I am the only user of my network drive. I also am the only one who have access to it, so I don't need permission to view its data. Please suggest any thing that I missed on my codes.

Thanks.

Import CSV File Into Access DB
I've searched the forum for an easy to follow example that works on this topic but couldn't find one so hense my question.

I want to be to import a CSV file into an existing table within my access database. The CSV file has 3 columns which are Date, Amount and Description.

This code finds the file, opens it and then reads the contents. What I need help with is how to use this code to import into an existing access database. Any help would be appreciated.

Code:
Private Sub Import_Click()
Dim arrData() As String
Dim s As String
Dim i As Integer

Open "C:Card.csv" For Input As #1
Do While Not EOF(1)
Line Input #1, s
arrData = Split(s, ",")

For i = 0 To UBound(arrData)

Debug.Print arrData(i)

Next i

Loop
Close #1

End Sub

Access Import Wizard & VB 6
Hello!

I am currently migrating an application from Access 2000 to VB 6. All was going fine till I came to the following line which appears to be unique to Access.

DoCmd.RunCommand acCmdImport

This Access import wizard is critical to my application due to its flexability and GUI. Is there a way to code this Access command (or equivlant wizard) into Visual Basic 6? Thanks!

Import Access Form
I have a form that I made in Access 2000, I was wondering if it is possible to open/import that to a VB program.

Basically, I wrote up a program just for that database, but to enter data you still have to open up Access, and go to the form to enter data. It would be a lot more user friendly (and I would also be happy to learn this) to just have the form open up in the program.

Now, perhaps if I can't do that I could recreate the form (just some text-boxes, and combo-boxes), have the user enter the data and use SQL to update the original access form. However, it would be so much easier to just import it somehow.

If anyone has any ideas or thoughts, I would appreciate it.

Thanks~

Import XML Into Access 2000
I have searched many internet forums searching for an answer to this question: How can I import XML into Access 2000? OR how can I get XML into Access 2000 via VBA?

Import Txt File To Access
I have several txt files all with different fieldnames. Currently the database imports them into a table using transfer text. I need all the fields to be text but things like postcodes are going is as numeric fields. I am aware of the schema.ini files but cant see how I can get it to use the fieldnames from the file (because they always change) but set all fields to text. Is there any way other than programming??

I was thinking maybe read txt file to get the fieldnames and create table 1st
then I can set all fields format to txt
then import file

thought there may be a better/quicker way I am not aware of

Import From Excel To Access
Is there an easy way to do this? I need to copy the entire Sheet 1 (with the except of row one [headers]) to the end of a current table. Any ideas on how I can do this?

Thanks!

***EDIT***
Let me clarify here... I know about both ADO and TransferSpreadsheet (and Text), but I think ADO is going to be the easiest to do. I'm just not sure how I'm to select everything from A:1 to the end of Sheet 1 (thru the range) using and ADO commands.

Access Database Import
I am trying to import a text delimited file into a Access database. I can do this manually without any errors, but when I do it through the VB code using the input statement and adding records to the database, I get an error when...

Some of the fields I am importing have double quotes in the line ("). The VB code is treating them as a field separator while the Access import function does not.

Is there a way to cause the Access import function to start from within VB, or is there a way to get around the double quote problem?

Any help would be appreciated.

Import Access Form To VB App?
I've heard that it may be possible to import a form from an Access MDB file into a VB app.

Anyone else heard of this?

A quick search looks like there may be 3rd-party tools for this, but, three's a crowd.

Import From Access To Excel?
I Import from Access to Excel, but I have a problem when it is many rows to import to excel.
Somtimes it's over 65 000 rows I import to excel, and there is where the problem is. Excel only handle 65 000 rows per workcheet and I want the code automaticly create a new workcheet in excel after 65 000 rows. How do I do that?

Access Import Oddity? Chr(26)
anyone know what a chr$(26) is?

it appears as a box (like Cr or Lf) but its a 26
it causes access to think its at the end of the file even though its not

any help would be appreaciated.

Thanks
Static

Import Access Database
silly question, i know, but theres no easy way to import an access 2000 database, reports and quiries into vb is there?

cheers

Import Into An Access Database
Does anyone know the code to import a comma delimited text file into a new access table without creating the table first then looping through the text file to populate the table? Kind of like coding the import wizard?

Thanks,
Merlin

How To Import A Report From Access To A VB App?
Once upon a time someone in my company made a report in Access to make labels with the client's name, address, city and zip (each is a field in a "Clients" table).

Now I'm making a VB app and those labels are to be included in the app, so each label can be printed and glued to an envelope (just for you guys to understand what it is for).

In my app I have a form where the user selects wich Clients he wants to mail to, and therefore:
1- I have to tell to the report wich clients' name, address etc. will be in the report (report-alike "sheet")
2- Tell it to Print (since I have to create a Print button, but never coded anything related to a printer)
3- Put a "Mr." or "Mrs." in the line above the name line (yes, I have a gender field in the same table)

Can someone help me, especially in point 1 and 2?

Import This With Access Instead Of Excel?
Can anyone help me to change this code to import via Access instead of Excel.
I import a table with including fields from another system (SAP) to excel. Now I wanna do this in Access.

The tablename in Access is Test1.
In this exemple, excel import to a Worksheet.

Here is the code:

VB Code:
Function Excel_Import() Dim iRow, iColumn, iStart, iStartRow As IntegerDim SEL_TAB, NAMETAB, TABENTRY, ROW As Object iStartRow = 1 iColumn = 1 For iRow = iStartRow To TABENTRY.RowCount For iColumn = 1 To NAMETAB.RowCount iStart = NAMETAB(iColumn, "OFFSET") + 1' If this is the last column, calculate the length differently than the other columns If iColumn = NAMETAB.RowCount Then iLength = Len(TABENTRY(iRow, "ENTRY")) - iStart Else iLength = NAMETAB(iColumn + 1, "OFFSET") - NAMETAB(iColumn, "OFFSET") End If' If the fields at the end of the record are blank, then explicitly set the value If iStart > Len(TABENTRY(iRow, "ENTRY")) Then Cells(iRow, iColumn) = Null Else Cells(iRow, iColumn) = Mid(TABENTRY(iRow, "ENTRY"), iStart, iLength) End If NextNext End Function

Import From Excel To Access...how?
I want to import a worksheet in an excelfile into a new table in access. The tablename should have the same name as the worksheet. The fieldnames in access should be the same as the fieldname in excel. How do I do that?

Import .dbf File To Access And ...??
Dear all,

I've two questions on using ADO in VB,
hope someone would give me some idea, thanks a lot.

1) How to import a dbf file to Access using codes in VB?

2) I may want to add a field to a table in access with datatype
set as " automatic number". How to do this in VB codes?

Thanks for all replies!!!
regards,
Meeko

Import Automation - MS ACCESS
Hey All,

Is it possible to automate the importing of a comma delimited text file into an access database?

Any Ideas?

Thanks.

Automate Import Into Access
I have a text file that is created via a query that runs nightly. Is there to automate that text file to import into access?

Import A .bas File With VBA In Access
Hi,

I exported a module to a .bas file. Now was my question if it's possible to import the file into a database using VBA code.

Import Spreadsheet To Access Using VB
Anyone who knows how to transfer data from spreadsheet to access? I tried to search this forum, but most of them displays the docmd.transferspreadsheet. I haven't used docmd.transferspreadsheet... how can i use the docmd.transferspreadsheet and where am i going to run it

How 2 Import Access Forms Into VB6
I have visual studio 6, and want to update an application I wrote in Access XP, but how do you import forms from access to visual basic.

This must be possible, please help

How To Import And Access An EXE Into VB Project
Hi All,
I want to import an exe file which has got menu into a VB Project. I am aware that i can do this with Shell Command. But the underlying problem here is that I wanted to click a menu in the exe, programmatically. Please guide

Import DBF (Foxpro DB) To Access??
Please help.

I am trying to access a Foxpro dbf file called regitem.dbf with Visualk Basic and I am not having any luck at all.

1. Does anybody know how tro do this easily?

2. If not can I import the DBF to an Access DB easily with Visual Basic 6 ?

3. Or can I use some other utility to import it to access than use VB to open the Access table?

Thanks

Import Xml File To Access Using VB
I am trying to import xml files from a SharePoint site everytime a new file is added. I have the file names in the format SAForm2008-04-23T15_08_36.xml and the date and time will change every time a new file is added to the site. What code do I use to autmatically import the files each time a new one is added? Right now I have Access on a timer to import files but it is importing ALL files not just the new ones. I know it can be done just don't know enough to make it happen.

Import SMS Messages To Access DB
I'm not sure if this is the right forum so I'm going to post this in a couple of places. Apologies for the duplication.

I need to automatically import SMS text messages into a table in an access database. The SMS messages are basically coming in as a simple comma delimited text file with 6 or 7 pieces of data in each row (eg, itemnum, temp, battery, sample).

It's easy to use the "get external data" command and manually import the file that way, but I'm going to be receiving one message every 30-45 minutes and am looking for an application or some code that will automatically retrieve the messages and do the import in one step.

Ideally the code/application could be scheduled to run frequently or just to sweep a folder location for any new files once a day and import them all to the same table, appending rows as it imports.

Anyone heard of anything like this before or have any suggestions?

Thanks,

Mia

Import Fox Pro Table Into Access Using VB
Within an Visual Basic I want to be able to import a Fox Pro .dbf into an Access database. Does anyone have a code sample on this or any suggestions? Thanks...

How Do I Import A CSV File Into Access
Hi!

From within access I'd like to use SQL to import all the records from a .csv file to a new table.

I'd really appreciate any directions or suggestions.

Thanks in advance,
Paolo.

Import Dbase To Access
Here's my case :
I'm trying to import a dBase IV database table (the content only) into a Access 2k DB using ADO.
It seems that every webpage I run into, I bump into a wall...
I've been getting errors like :
  - IMAP not found
  - Syntax error in my SQL string
  - can't connect to database
  - etc, etc...
Here's a fraction of the code I use :
Code:
Dim strDBAse As String
Public cn As ADODB.Connection
Dim strTable As String
Dim strSQL As String
Public rs As ADODB.Recordset
Public rs2 As ADODB.Recordset

Private Sub Command1_Click()
    CommonDialog1.Filter = "DBase Files/*.dbf"
    CommonDialog1.ShowOpen
    Text1.Text = CommonDialog1.FileName

End Sub

Private Sub Command2_Click()
        FileNameStr = ""
        FilePathStr = ""
        FoundFN = "No"
    For I = 0 To Len(CommonDialog1.FileName) - 1
        If Mid$(CommonDialog1.FileName, (Len(CommonDialog1.FileName)) - I, 1) <> "" And FoundFN <> "Yes" Then
            FileNameStr = Mid$(CommonDialog1.FileName, (Len(CommonDialog1.FileName)) - I, 1) & FileNameStr
        Else
            FoundFN = "Yes"
            FilePathStr = Mid$(CommonDialog1.FileName, (Len(CommonDialog1.FileName)) - I, 1) & FilePathStr
        End If
    Next I

Dim Db As New ADODB.Connection
Dim Db2 As New ADODB.Connection


Db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "AccDB.mdb;Persist Security Info=False"

Db2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FilePathStr & ";Extended Properties=dBASE IV;"

From here... It gets dirty
Code:
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenKeyset
rs2.LockType = adLockOptimistic

strSQL = "SELECT * FROM Data_tbl"
rs.Open strSQL, Db, , , adCmdText

strSQL = "SELECT * FROM [polygon_lots_]"
rs2.Open strSQL, Db2, , , adCmdText

strSQL = "DROP TABLE Data_tbl"
'Db.Execute strSQL

strSQL = "Insert into 'D:VBAppsImportDBFprintReportAccDB.mdb' Lot from polygon_lots_"

strSQL = "INSERT INTO Data_tbl IN 'D:VBAppsImportDBFprintReportAccDB.mdb'" & _
            " SELECT * FROM 'polygon_lots_' 'dBase IV'"
Db.Execute strSQL

The last portion of the code is just me trying to find some sort of way to make it work...
There are extra lines not needed.

Any1 ?