SOLVED: Get External Data

[banavas]

Dear Friends,

does anyone of you knows a way (macro or any other piece of code) to automate the Get External Data process is Access?

Thank you in advance,
G.

[SJ McAbney]

If the data is in a spreadsheet, use the TransferSpreadhseet method.
If the data is in another database, use the TransferDatabase method.

[babsc01]

It all depends on the type of data you want to import. You can use these methods for other Access data:

OpenTable, OpenQuery, OpenForm, OpenReport, OpenModule

If it's an Excel file that hasn't been imported to an Access table, use:

TransferSpreadsheet

Text files would use TransferText

The preferred way for me to import data is by query. You can easily create macros and modules to automate your queries. Good luck!

[GP George]

Dear Friends,

does anyone of you knows a way (macro or any other piece of code) to automate the Get External Data process is Access?

Thank you in advance,
G.

There is no generic "Get External Data" Process as such in Access.

Can you provide more detail about the source of the external data and the way you want to use it?

George

[banavas]

The data is in *.txt format and we are talking about transactions data. The question is how can I do something like:

' Open Ascii
Workbooks.OpenText filename:=filenameL, _
Origin:=1253, _
StartRow:=12, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(6, 2), _
Array(12, 1), _
Array(23, 1), _
Array(41, 1), _
Array(57, 1), _
Array(74, 1), _
Array(91, 1)), _
DecimalSeparator:=".", _
ThousandsSeparator:=",", _
TrailingMinusNumbers:=True

in access environment. Create for example a macro in vb that will read my data. I can easily do it in excel but the thing is that my data records are more than 66000. Does any of you has any piece of code where I can automate this process with modules and macros?

Thanks!

[GP George]

There are a couple of ways to accomplish what I think you want to do.

First, with Access you can link to a text file and treat it the same as any other table. Of course, that works if the text file is in the proper format.

The other method is to use the TransferText method of the DoCmd Object:

Here is the syntax:

DoCmd.TransferText acImportFixed, , "tblTemp", "C:\MyFiles\DataFile.txt"

where "tblTemp" is the name of the Access table into which you want to import the file, and "C:\MyFiles\DataFile.txt" is the name of the text file.

You can create an Import Specification to control how the text file is imported. Search Access Help for TransferText to learn more about the specifics.

HTH

George

[flavo]

Did an example code for this to use in Excel. Need to refrence Microsoft DAO Object Ligbrary 3.?? - To do this, in the VB window select TOOLS - REFRENCES and tick it from the list

Function GetAccessValuesIntoExcel() As Boolean
'will return True if it works, false if it failed.

On Error GoTo Exit_GetAccessValuesIntoExcel
GetAccessValuesIntoExcel = False
Dim oJet As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lRows As Long

'creat Jet workspace
Set oJet = CreateWorkspace("", "admin", "", dbUseJet)
'open jet db
Set db = oJet.OpenDatabase("C:\myDirectory\myDatabase.mdb")
'open recordset
Set rs = db.OpenRecordset("SELECT ID, myField FROM tblMyTable")

'print filed titles
Cells(1, 1).Value = rs.Fields(0).Name
Cells(1, 2).Value = rs.Fields(1).Name
lRows = 2
'print recordset values to the excel

While Not rs.EOF
Cells(lRows, 1).Value = rs("ID").Value
Cells(lRows, 2).Value = rs("MyField").Value
lRows = lRows + 1
rs.MoveNext
Wend

GetAccessValuesIntoExcel = True

Exit_GetAccessValuesIntoExcel:
'clean up
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
oJet.Close
Set oJet = Nothing

End Function

Dave

[banavas]

Thank you GP George. TransferText is probably the solution to my problem. However, how should I create an Import Specification. Do you have an example? The access help is not that informative on that.

Thanx,
G

[banavas]

Dear Friends,

after created the schema.ini file for the TranferText method I got the following error:

Run-time error '3625':
The text file specification 'C:workSchema.ini' does not exist. You cannot import, export, or link using the specification.

Any ideas how to solve that? I found the 241477 article of Microsoft that references the problem but I have no idea how to apply it. Any ideas on that?

Thanks,
G.

[flavo]

Dont use the schema.ini, a little hard t plau with.

Do a "dummy import" using the wizzard (file - get external data), then in the last screen before you hit Finish, click advanced and follow you nose to the bit that says "save specification". Save it as say "mySpec". You can then use this rather than the schema.ini

Hope i expaned this well enough.

Dave

[banavas]

Thank you! Please consider the thread closed. G.