[Home] [Downloads] [Search] [Help/forum]

Gammon Forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  VBscript
. . -> [Subject]  Sql connection pulling odd data
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?

Sql connection pulling odd data

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page


Posted by Ignis   (26 posts)  [Biography] bio
Date Fri 06 Mar 2015 06:15 PM (UTC)
Message
It looks like this error is partially based on default directories in the program somewhere. I have no plugins running, nor script files, everything happens within the triggers and aliases themselves.

I'm having troubles with a sql connection i'm trying to setup, the idea is to connect it directly to an external drive so in case of system failure it's already backed up. but I keep running into the following issues. This worked before when i was using a different connection method to a MySql Database, however trying to convert it to use an access database is where i'm running into issues.


Could not find file 'C:\Program Files (x86)\MUSHclient\dbwod.mdb'.

after doctoring the following line:

connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\junk" & chr(92) & "WOD mud" & chr(92) & "dbwod.accdb")

if I have it as

connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\junk\WOD mud\dbwod.accdb")


then I start losing \


World: tiopon.mudmagic.com
Execution of line 25 column 1
Immediate execution
Could not find file 'F:\junkWOD muddbwod.accdb'.


full code here:

' enforces variable names and declarations
option explicit

Dim connectionString
Dim Connection
Dim Recordset
Dim SQL
Dim Server
Dim field
Dim item

'declare the SQL statement that will query the database
SQL = "select * from dbwod.relics"


'create an instance of the ADO connection and recordset objects

Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")
connectionString = ""

'open the connection to the database
connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\junk\WOD mud\dbwod.accdb")

Connection.Open connectionString 'open using above info


'Open the recordset object executing the SQL statement and return records 
Recordset.Open SQL,Connection
world.note SQL

'first of all determine whether there are any records 
If Recordset.EOF Then 
simulate "There are no records to retrieve." & vbcrlf

Else 
'if there are records then loop through the fields 
Do While NOT Recordset.Eof   

For each item in Recordset.Fields
  simulate (Recordset(item.Name)) & "|"
 Next
Simulate vbcrlf
'field = Recordset.Fields()
'if field <> "" then
'Simulate field & vbcrlf
'end if
Recordset.MoveNext     
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #1 on Fri 06 Mar 2015 08:55 PM (UTC)

Amended on Fri 06 Mar 2015 08:56 PM (UTC) by Nick Gammon

Message
Within triggers and aliases you have to double backslashes.

And if you are going to post them in the forum you have to double them again. :)

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Ignis   (26 posts)  [Biography] bio
Date Reply #2 on Fri 06 Mar 2015 11:14 PM (UTC)

Amended on Fri 06 Mar 2015 11:16 PM (UTC) by Ignis

Message
I tried that, it returns an error as though it's still searching a directory on my c drive, even though i'm trying to point to f:


path:
F:\\junk\\WOD mud\\dbwod.accdb

error:

Script error
World: tiopon.mudmagic.com
Execution of line 29 column 1
Immediate execution
Could not find file 'C:\Program Files (x86)\MUSHclient\dbwod.mdb'.


just to verify to file path i ran this:

<aliases>
  <alias
   match="verxfile"
   enabled="y"
   group="dataValidation"
   send_to="12"
   sequence="100"
  >
  <send>Dim filepath

Set objFSO = CreateObject("Scripting.FileSystemObject")
filepath = "F:\\junk\\WOD mud\\dbwod.accdb"
note "Search Results as follows for: " &amp; filepath
If objFSO.FileExists(filepath) Then
    note "Found."
Else
    note "The file does not exist."
End If</send>
  </alias>
</aliases>



and the return I got was :


Search Results as follows for: F:\junk\WOD mud\dbwod.accdb
Found.


I am strugling as this does seem like something right in front of my nose I am missing, any further insight would be greatly appreciated.
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #3 on Fri 06 Mar 2015 11:19 PM (UTC)
Message
I haven't used ADODB for a while now (with good reason, I think) but as I recall there is an ODBC control panel that lets you set up the path of the database.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Ignis   (26 posts)  [Biography] bio
Date Reply #4 on Sun 08 Mar 2015 06:04 PM (UTC)
Message
I did more digging, apparently avoiding the odbc route is very difficult in win7 64 connecting to a 32bit accdb, so final code connects to an mdb using an ocdb. There's another thread already that shows a solution to connecting to a sql database, and my code was similar enough i thought it more respectful to omit my code and refer to searching out the other thread.
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #5 on Sun 08 Mar 2015 11:01 PM (UTC)
Message
These days I would use SQLite3 databases if possible.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Ignis   (26 posts)  [Biography] bio
Date Reply #6 on Mon 09 Mar 2015 03:08 AM (UTC)
Message
It wasn't as hard as previously thought, switching the db structure to 2003 *.mdb as a save as option in Access 2013 I just needed to change the connection string to the following:


connectionString = "Driver={Microsoft Access Driver (*.mdb)};" &_
"Dbq=F:\junk\WOD mud\DBWod.mdb;" &_
"Uid=Admin;" &_
"Pwd=;"


this allowed me to sync to an external drive without an ODBC
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


4,049 views.

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at FutureQuest]