Sql connection pulling odd data

Posted by Ignis on Fri 06 Mar 2015 06:15 PM — 7 posts, 30,352 views.

Ignis Fri 06 Mar 2015 06:15 PM #0

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

Nick Gammon Australia Forum Administrator Fri 06 Mar 2015 08:55 PM #1

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. :)

Ignis Fri 06 Mar 2015 11:14 PM #2

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.

Nick Gammon Australia Forum Administrator Fri 06 Mar 2015 11:19 PM #3

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.

Ignis Sun 08 Mar 2015 06:04 PM #4

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.

Nick Gammon Australia Forum Administrator Sun 08 Mar 2015 11:01 PM #5

These days I would use SQLite3 databases if possible.

Ignis Mon 09 Mar 2015 03:08 AM #6

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