Sign in to follow this  
Followers 0
JSThePatriot

AutoIt and Databases.

35 posts in this topic

I just ran Windows Spy through an access database, and it wont be near as easy as I had originally thought, but I still think it can be done emulating the keyboard.

I had mentioned to some people that I thought this would be an easy process and wanted to let everyone know it wont be as easy as I thought so, sorry :)

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites



That is good... I may be able to use that in an upcoming job :). I appreciate that. I am fixing to have to navigate a database to get usernames and passwords and have autoit go through and get the password and paste it into another program that is supposed to be encrypting files for me. We will see how this goes...

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

One thing I found very useful is copying info to the clipboard from access, and then having AutoIt parse it and fill out forms and anything else that way. You can make a form button, and then have that button do a VB script.

The below sends a pipe | delimited record into clipboard.

Private Sub Command259_Click()
ClipBoard_SetData (Me.txtWebNumber & "|" & Me.fldFinalProductTotal & "|" & Me.fldFinalShippingTotal & "|" & Me.fldFinalTaxTotal & "|" & Me.fldFinalGrandTotal & "|" & Me.txtName & "|" & Me.txtCompany & "|" & Me.txtAddress & "|" & Me.txtAddress2 & "|" & Me.txtCity & "|" & Me.txtState & "|" & Me.txtZip & "|" & Me.txtCountry & "|" & Me.txtPhone & "|" & Me.txtPay1 & "|" & Me.txtPay4)
DoCmd.RunMacro ("link")
End Sub

You also need to make a module for it. Go to modules, select new, and paste this in. Name it To_clipboard

This was free code I got that has been very helpful to me, the original autor was unkown to where I got, or I would have included his/her name in a comment.

Option Compare Database

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
         As Long
      Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
         As Long
      Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
         ByVal dwBytes As Long) As Long
      Declare Function CloseClipboard Lib "User32" () As Long
      Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
         As Long
      Declare Function EmptyClipboard Lib "User32" () As Long
      Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
         ByVal lpString2 As Any) As Long
      Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
         As Long, ByVal hMem As Long) As Long

      Public Const GHND = &H42
      Public Const CF_TEXT = 1
      Public Const MAXSIZE = 4096
      

Function ClipBoard_SetData(MyString As String)
         Dim hGlobalMemory As Long, lpGlobalMemory As Long
         Dim hClipMemory As Long, X As Long

         ' Allocate movable global memory.
         '-------------------------------------------
         hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

         ' Lock the block to get a far pointer
         ' to this memory.
         lpGlobalMemory = GlobalLock(hGlobalMemory)

         ' Copy the string to this global memory.
         lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

         ' Unlock the memory.
         If GlobalUnlock(hGlobalMemory) <> 0 Then
            MsgBox "Could not unlock memory location. Copy aborted."
            GoTo OutOfHere2
         End If

         ' Open the Clipboard to copy data to.
         If OpenClipboard(0&) = 0 Then
            MsgBox "Could not open the Clipboard. Copy aborted."
            Exit Function
         End If

         ' Clear the Clipboard.
         X = EmptyClipboard()

         ' Copy the data to the Clipboard.
         hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

         If CloseClipboard() = 0 Then
            MsgBox "Could not close Clipboard."
         End If

End Function

If it was just a lookup, I would use SQL from commandline like sugested. If you are just learning, many SQL can be done by using Access builder, and then change from design view to SQL view. Only issue is that all SQL have different flavors, and there are slight differences at times.


AutoIt3, the MACGYVER Pocket Knife for computers.

Share this post


Link to post
Share on other sites

I am not just learning, though I need to retrieve a list of passwords and then use each one in that list to where it matches up to a MemberID # in another program. I was thinking autoit could do that for me... that way I wouldnt have to search through 13k records every time I needed to do this...

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Aye, autoit would work great, and you wouldn't even have to have access open it. I would use SQLCMD.

The instructions tell ya how, but basically it would be something like:

$command=StringReplace('SELECT Names.Password FROM info where left(name,1)=#bob#;','#',"'"); I used bob, but you could make a variable.
Run('sqlcmd.exe /db "MS Access Database" /silent /log "test.txt" /command "' & $command & '"','',@SW_MINIMIZE)

Then read and parse the text file, and rewrite over it. (for security)

Anyway that might get you started. You can set up a SQL to Access link in control panel under administration tools and then database sources.


AutoIt3, the MACGYVER Pocket Knife for computers.

Share this post


Link to post
Share on other sites

Thank you scriptkitty. I will be checking on this. This program I am going to have to create will also have to FTP files back up to a website... I think I have seen a command line FTP program, or is there one included in WinXP?

I will also have to learn directory and file commands because I will need to look in a directory for a certain MemberID.pdf and then check that sql to find the password by matching up the MemberID and then I need it to open another program and input the full path to the MemberID.pdf and put in the password from the matchup, and then save the file to a new directory and then upload all files when done.

Let me know if you have any tips or hints.

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

What is wrong with this? I have tried reading the sqlcmd.txt and I cant figure out why this isnt working...

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family"
Dim $db = "fcadata.mdb"

Run('C:/Tools/sqlcmd.exe /db"' & $db & '" /silent /log "test.txt" /command "' & $sql & '"','',@SW_MINIMIZE)

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

What is wrong with this? I have tried reading the sqlcmd.txt and I cant figure out why this isnt working...

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family"
Dim $db = "fcadata.mdb"

Run('C:/Tools/sqlcmd.exe /db"' & $db & '" /silent /log "test.txt" /command "' & $sql & '"','',@SW_MINIMIZE)

JS

<{POST_SNAPBACK}>

The database is not going to be a file it will be a predefined ODBC connection. Notice in scriptkitty's example he used "MS Access Database" not "MS Access Database.mdb". This is defined in the ODBC connections dialog under the control panel.

*** Matt @ MPCS

Share this post


Link to post
Share on other sites

The database is not going to be a file it will be a predefined ODBC connection. Notice in scriptkitty's example he used "MS Access Database" not "MS Access Database.mdb". This is defined in the ODBC connections dialog under the control panel.

*** Matt @ MPCS

<{POST_SNAPBACK}>

Thanks I was wondering about that...

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Is there a way to delete the first 8 lines of a file?

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Is there a way to delete the first 8 lines of a file?

JS

<{POST_SNAPBACK}>

Sure there is but if you are doing it with a database I don't suggest it.

*** Matt @ MPCS

Share this post


Link to post
Share on other sites

Oh not a database... I will show you my code and a sample of the text file.. I am running into a wall here...

The first part of the code makes a file with the following output.

Logging into 'FCA Database' using account ''...

:CMD BEGIN:

SELECT Family.MemberID, Family.MemberPassword FROM Family

:CMD END:

                    Name      Index      Width Type  XLT Description

                MemberID [ 1 of  2]        11    4    3 Integer

          MemberPassword [ 2 of  2]        10  12    2 Char

'4' 'Mark519'

'5' '1Tim211'

'6' 'Zech91'

'10' 'Eph618'

'11' 'Lev1316'

'13' '1King141'

'14' 'Jer124'

'15' 'Zech85'

First Part

$g_szVersion = "Open Access"
If WinExists($g_szVersion) Then Exit; It's already running
AutoItWinSetTitle($g_szVersion)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family"
Dim $db = "FCA Database"
Dim $file, $line, $linewrite
Dim $file1 = "text.txt"
Dim $file2 = "test.txt"

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

Second Part

$file1 = FileOpen($file1, 0)
If $file1 = -1 Then Exit

While 1
   $line = FileReadLine($file1)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
WEnd

The second part of the code makes the following output.

Logging into 'FCA Database' using account ''...

:CMD BEGIN:

SELECT Family.MemberID, Family.MemberPassword FROM Family

:CMD END:

                    Name      Index      Width Type  XLT Description

                MemberID [ 1 of  2]        11    4    3 Integer

          MemberPassword [ 2 of  2]        10  12    2 Char

'4|Mark519'

'5|1Tim211'

'6|Zech91'

'10|Eph618'

'11|Lev1316'

'13|1King141'

'14|Jer124'

'15|Zech85'

What I am trying to do is just get it down to a list of the numbers on left and the bible verse on right. That way I can do a stringsplit() on the '|' and have an array that AutoIt can then reference to search through so it can find the proper memberID and then input the bibleverse into another program.... Let me know if this is possible...

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Oh not a database... I will show you my code and a sample of the text file.. I am running into a wall here...

The first part of the code makes a file with the following output.

First Part

$g_szVersion = "Open Access"
If WinExists($g_szVersion) Then Exit; It's already running
AutoItWinSetTitle($g_szVersion)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family"
Dim $db = "FCA Database"
Dim $file, $line, $linewrite
Dim $file1 = "text.txt"
Dim $file2 = "test.txt"

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

Second Part

$file1 = FileOpen($file1, 0)
If $file1 = -1 Then Exit

While 1
   $line = FileReadLine($file1)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
WEnd

The second part of the code makes the following output.

What I am trying to do is just get it down to a list of the numbers on left and the bible verse on right. That way I can do a stringsplit() on the '|' and have an array that AutoIt can then reference to search through so it can find the proper memberID and then input the bibleverse into another program.... Let me know if this is possible...

JS

<{POST_SNAPBACK}>

This can be done a couple ways. I would probably do this:

$file3 = FileOpen( "OutputFile.txt", 0 )

$i = 8
While 1
   $line = FileReadLine($file1, $i)
   If @error = -1 Then ExitLoop
   FileWriteLine($file3, $linewrite)
   $i = $i + 1
WEnd

I don't know if you want to include the eighth line or start with the nineth but you can see how it works. I don't know if I really suggest this method because it isn't very modular and assumes you ALWAYS want to remove the top 8 lines. It could probably be more encapsulated. Hope this helps though.

*** Matt @ MPCS

Edited by Matt @ MPCS

Share this post


Link to post
Share on other sites

Well for this application and what I am doing it will only need to remove the top 8 lines and start with the 9th.

Would that work for that?

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Well for this application and what I am doing it will only need to remove the top 8 lines and start with the 9th.

Would that work for that?

JS

<{POST_SNAPBACK}>

Try it and let me know... :)

*** Matt @ MPCS

Share this post


Link to post
Share on other sites

This can be done a couple ways. I would probably do this:

$file3 = FileOpen( "OutputFile.txt", 0 )

$i = 8
While 1
   $line = FileReadLine($file1, $i)
   If @error = -1 Then ExitLoop
   FileWriteLine($file3, $linewrite)
   $i = $i + 1
WEnd

I don't know if you want to include the eighth line or start with the nineth but you can see how it works. I don't know if I really suggest this method because it isn't very modular and assumes you ALWAYS want to remove the top 8 lines. It could probably be more encapsulated. Hope this helps though.

*** Matt @ MPCS

<{POST_SNAPBACK}>

I just dont understand how that is going to forget about the first 8 lines. What that appears to be doing is only copying the first 8 lines to the output.txt.

Let me know... I will try it when I get back on later...

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

I just dont understand how that is going to forget about the first 8 lines. What that appears to be doing is only copying the first 8 lines to the output.txt.

Let me know... I will try it when I get back on later...

JS

<{POST_SNAPBACK}>

FileReadLine has a parameter that is which line it is going to read. What the code does is it starts at the 8th line and copies until the end of the file. I thought from the scripts you have posted that you would have gotten that. It actually starts at whatever you set $i to before the loop... in the example I started at 8 but from what you said you probably need to change the $i to 9 instead. Make sense or do I have to dumb it down more for you?

*** Matt @ MPCS

Share this post


Link to post
Share on other sites

LOL no you dont have to dumb it down for me. :-P From what I read...

The line number to read.

This in no way indicated that it would read from that line to the end of the file. If it would have clearly stated that then I wouldnt have had to ask you a question about it. Please dont take my questions for being stupid before checking the logic behind which made me ask that question. I am against asking stupid questions, so I know how you feel about that but you will find that I do not ask them.

JS


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

LOL no you dont have to dumb it down for me. :-P From what I read...

This in no way indicated that it would read from that line to the end of the file. If it would have clearly stated that then I wouldnt have had to ask you a question about it. Please dont take my questions for being stupid before checking the logic behind which made me ask that question. I am against asking stupid questions, so I know how you feel about that but you will find that I do not ask them.

JS

<{POST_SNAPBACK}>

Well this is one of the few times I will apologize for treating someone as stupid. You have shown so far that you are smarter than a pencil, but still have quite a while to go before you become a stapler j/k. I figured the code spoke for itself. If you have questions regarding code make sure you understand what you think each line of the code does and ask specific questions regarding the the purpose of a function or a couple of lines of code... do not just ask if it works or what it does. If you expect to be treated as though you have more than a single brain cell... form your questions intelligently enough where I can assume you do. There is no offense intended by this statement, this is how I try to formulate my posts so I hold others on the same level.

*** Matt @ MPCS

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0