DDEPoke to MSAccess

[pgriffin]

I have an Access database which contains two tables. I need to perform a ddepoke to a specific table and field with data I am reading from a text file.
I use DDERequest/DDEPoke only with Excel so far. Is this possible?

HELP!

[support]

Don't think Access supports DDE. Instead use VBScript/ODBC/ADO/SQL:
http://www.mjtnet.com/index.htm?vbsdb.html

[mydave]

I tried the link, but dont know what I should look for in that link.
Anyway, this topic is the one I've been looking for the answer for sometimes. And now, it is most urgent.

SkunkWorks, have you get your script done yet?

Assuming that I have a text file, with coma delimeters, as follows:

FirstName,LastName,Address,Phone,Date_Purchase,Invoice_Number,Item1,Item2,Item3

I have two tables in Access, table_Customers and table_Sales.
table_Customers has FirstName,LastName,Address,Phone
table_Sales has "Date_Purchase,Invoice_Number,Item1,Item2,Item3"

I would like to see a sample how this was accomplished.
Thanks

[Marcus Tettmar]

Hi,

Go to:
http://www.mjtnet.com/vbsdb.htm

This is exactly what you need. This is a tutorial on how to access data in Access. You should work through that example and you will then understand what you need to do. The only real difference is that your SQL will be different.

When you have run through that example come back to the forums here and search for "ADODB" and you will find at least 20 matches with information on accessing databases.

[Marcus Tettmar]

I tried the link, but dont know what I should look for in that link.
Anyway, this topic is the one I've been looking for the answer for sometimes. And now, it is most urgent.

SkunkWorks, have you get your script done yet?

Assuming that I have a text file, with coma delimeters, as follows:

FirstName,LastName,Address,Phone,Date_Purchase,Invoice_Number,Item1,Item2,Item3

I have two tables in Access, table_Customers and table_Sales.
table_Customers has FirstName,LastName,Address,Phone
table_Sales has "Date_Purchase,Invoice_Number,Item1,Item2,Item3"

I would like to see a sample how this was accomplished.
Thanks

BTW - You don't need to use Macro Scheduler to do this at all - if you just want to import data from a CSV into two tables all you need is a couple of queries in Access - Access can already do this. I might evangelise Macro Scheduler, but for this it is complete overkill!

[mydave]

Thanks Marcus, I am reading the instructions now.
Also thanks for reminding me about using append queries in Access.
Hope that by the end of the day, I'll could get my way out.

[Marcus Tettmar]

No problem. If you DO need/want to use Macro Scheduler here's an example that works with your quoted scenario of a CSV file and an access DB with two tables:

//InsertData function to insert data into two tables
VBSTART
Sub InsertData(fname,lname,addr,phone,idate,inum,item1,item2,item3)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
'MyDB.Mode = adModeShareExclusive
'CHANGE PATH TO MDB IN NEXT LINE
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\dbex\db1.mdb;"

'insert into table_Customers
SQLString = "INSERT INTO table_Customers (FirstName, LastName, Address, Phone) " & _
"VALUES ('" & fname & "', '" & lname & "', '" & addr & "', '" & phone & "');"
MyDB.Execute(SQLString)

'insert into table_Sales
SQLString = "INSERT INTO table_Sales (Date_Purchase, Invoice_Number, Item1, Item2, Item3) " & _
"VALUES ('" & idate & "', '" & inum & "', '" & item1 & "', '" & item2 & "', '" & item3 & "');"
MyDB.Execute(SQLString)

MyDB.Close
End Sub
VBEND

//Read through the CSV file
Let>lNum=1
Label>ReadFile
//read a line in from the file
ReadLn>%SCRIPT_DIR%\input.txt,lNum,line
//if we have reached end of file jump out of loop
If>line=##EOF##,DoneReadFile
//separate comma separated values into separate variables
Let>comma=,
Separate>line,comma,fields
//run the VBS SQL functions to insert into the tables
VBRun>InsertData,fields_1,fields_2,fields_3,fields_4,fields_5,fields_6,fields_7,fields_8,fields_9
Let>lNum=lNum+1
Goto>ReadFile
Label>DoneReadFile


I've also uploaded a zip file containing the CSV file, the access MDB file and the script file so you can even try it out: http://www.mjtnet.com/demos/dbex.zip

Enjoy!

[mydave]

Marcus, Yes, yes, yes, yes, yes . . . . . . yes!
Most clear and defined. I tried the 'upload' and it worked just as expected. I can build my app from here, I think, and with the better way.
I most rather use msScheduler to do the task than to use Access queries in command line, because msScheduler is Elegant, Quick and Efficient. For non-programmer (myself), it is a bridge to cross when help is not available. For programmers, it is a a time saver, and perhaps, a indespensible tool.
Thanks again, Marcus.

[mydave]

Marcus, need help, please ! ! !

Input table:

Cell2,46605485,Passed,Passed,20060512
Cell2,46605070, 731, EPS #2 MECHANICAL MISALIGNMENT BETWEEN CAMSHAFT AN,20060518
Cell2,46605069, NONE, NONE,20060510

Database, tblMain:

ID - AutoNumber
Cell - Text
ESN - Text
FailCode - Text
Description - Text
Data - Text

Script:

Let>APP_TITLE=Test Data Into Access
//InsertData function to insert data into tblMain table
VBSTART
Sub InsertData(cell, esn, fcode, descrip, date)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
'MyDB.Mode = adModeShareExclusive
'CHANGE PATH TO MDB IN NEXT LINE
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Faultcodes\Result\Test.mdb;"

'insert into table_tblMain
SQLString = "INSERT INTO table_tblMain (Cell, ESN, FailCode, Description, Date) " & _
"VALUES ('" & cell & "', '" & esn & "', '" & fcode & "', '" & descrip & "', '" & date & "');"
MyDB.Execute(SQLString)

MyDB.Close
End Sub
VBEND

//Read through the CSV file
Let>lNum=1
Label>ReadFile
//read a line in from the file
ReadLn>c:\Faultcodes\Result\NewSummary.txt,lNum,line
//if we have reached end of file jump out of loop
If>line=##EOF##,DoneReadFile
//separate comma separated values into separate variables
Let>comma=,
Separate>line,comma,fields
//run the VBS SQL functions to insert into the tables
VBRun>InsertData,fields_1,fields_2,fields_3,fields_4,fields_5
Let>lNum=lNum+1
Goto>ReadFile
Label>DoneReadFile

When the script ran, I got error message:

Microsoft JET Database Engine: -2147217900
Syntax error in INSERT INTO statement.
Line 14, Column 2

Line 14, Column 2 is the space in front of MyDB.Execute(SQLString).

What did I do wrong?
Thanks

[Marcus Tettmar]

It may have been a type nut in your table definition you have the last column as Data but in the code it is Date. Probably just a type. So if it's not that ....

Put this line just before the MyDB.Execute line so that you can see what data it is passing in:

MsgBox SQLString

Also step through the script.

Which line is the one that causes the error - it may not be the first one if it is something to do with the data format? Does the SQL look ok in the message box? What does it show?

[mydave]

Hi,
It was a typo error with respect to table def (Date, not Data).

With 'MsgBox SQLString' added, when the script ran, it displayed:

INSERT INTO table_tblMain (Cell, ESN, FailCode, Description, Date) VALUES ('Cell2','46605485','Passed','Passed','20060512');

the bottom of the message has an OK prompt, which I clicked, and I got the same error message as posted earlier.

I reran the script, by stepping thru the script, line by line. It went to the VBRun line, displayed the value content (as shown above), then the error message. I think the the error line is MyDB.Execute(SQLString)

Content of the log file:

6/5/2006 13:24:18:328 - Started Macro : C:\FaultCodes\Result\Test Data Into Access.exe
6/5/2006 13:24:18:343 - START: Let>APP_TITLE=Test Data Into Access
6/5/2006 13:24:18:343 - END: Let>APP_TITLE=Test Data Into Access
6/5/2006 13:24:18:359 - START: //InsertData function to insert data into Main table
6/5/2006 13:24:18:359 - END: //InsertData function to insert data into Main table
6/5/2006 13:24:18:375 - START: VBSTART
6/5/2006 13:24:18:812 - END: VBEND
6/5/2006 13:24:18:828 - START:
6/5/2006 13:24:18:843 - END:
6/5/2006 13:24:18:843 - START: //Read through the CSV file
6/5/2006 13:24:18:859 - END: //Read through the CSV file
6/5/2006 13:24:18:859 - START: Let>lNum=1
6/5/2006 13:24:18:875 - END: Let>lNum=1
6/5/2006 13:24:18:890 - START: Label>ReadFile
6/5/2006 13:24:18:890 - END: Label>ReadFile
6/5/2006 13:24:18:906 - START: //read a line in from the file
6/5/2006 13:24:18:922 - END: //read a line in from the file
6/5/2006 13:24:18:922 - START: ReadLn>c:\Faultcodes\Result\NewSummary.txt,lNum,line
6/5/2006 13:24:18:937 - END: ReadLn>c:\Faultcodes\Result\NewSummary.txt,lNum,line
6/5/2006 13:24:18:953 - START: //if we have reached end of file jump out of loop
6/5/2006 13:24:18:968 - END: //if we have reached end of file jump out of loop
6/5/2006 13:24:18:968 - START: If>line=##EOF##,DoneReadFile
6/5/2006 13:24:18:984 - END: If>line=##EOF##,DoneReadFile
6/5/2006 13:24:19:000 - START: //separate comma separated values into separate variables
6/5/2006 13:24:19:015 - END: //separate comma separated values into separate variables
6/5/2006 13:24:19:015 - START: Let>comma=,
6/5/2006 13:24:19:031 - END: Let>comma=,
6/5/2006 13:24:19:047 - START: Separate>line,comma,fields
6/5/2006 13:24:19:062 - END: Separate>line,comma,fields
6/5/2006 13:24:19:078 - START: //run the VBS SQL functions to insert into the tables
6/5/2006 13:24:19:078 - END: //run the VBS SQL functions to insert into the tables
6/5/2006 13:24:19:094 - START: VBRun>InsertData,fields_1,fields_2,fields_3,fields_4,fields_5
6/5/2006 13:35:37:403 - END: VBRun>InsertData,fields_1,fields_2,fields_3,fields_4,fields_5
6/5/2006 13:35:37:419 - Finished Macro : C:\FaultCodes\Result\Test Data Into Access.exe

I stepped thru the script, line by line. It went to the VBRun line, displayed the
The error line is MyDB.Execute(SQLString)

By the way, I could import the same data into Access using the builtin importer, so I am pretty sure that the input file is clean.

Thanks for looking into this.

[Marcus Tettmar]

The SQL that is being displayed is valid syntax. Therefore I think you must have one of the data types wrong. Are you SURE of the table definition. Perhaps one of the fields is a numeric value rather than a string? Since the syntax is valid the only reason you'd get a type mismatch error is if one of the fields should be of a type other than has been specified. You have specified all as strings (in quotes) but if one is meant to be a numeric value then that would cause a type mismatch.

[mydave]

Thanks Marcus,
I will check again and again. perhaps from scratch.
Since you are confident about the SQL syntax, then I can work on the table and input file.
Sincerely,

[Marcus Tettmar]

One thing you can try is to type that SQL into Access directly and see what happens - you should get an error. Go into Access, create a new Query and go to SQL view and then type:

INSERT INTO table_tblMain (Cell, ESN, FailCode, Description, Date) VALUES ('Cell2','46605485','Passed','Passed','20060512');

Then RUN the query. You should get more of an idea what is wrong. You can build the query in Access graphically and then go back to SQL view to see what it should look like.

[mydave]

Thanks for the tip, Marcus,

I tested 'INSERT INTO table_tblMain (Cell, ESN, FailCode, Description, Date) VALUES ('Cell2','46605485','Passed','Passed','20060512');' and I got 'Syntax error in INSERT INTO statement'. After clicking OK, Date is highlighted. The Date field is tex property, so I am not sure why.

Will continue digging at it.

P.S. Using Access internal importer, it works fine.