Help with JET/Connection

[danmack]

Hi all,

The following code allows some users to update an access table (on a shared LAN) from an excel workbook (code is within an excel module).

Dim accApp As Object
    Dim strOrder As String, strCode As String
    'DFII = txtDFIName.Value
strSQL = "INSERT INTO [PDI] ([Part No],[PDI Date],[P_Score],[Risk_Score])" & _
 "VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"
 
Set accApp = CreateObject("Access.Application")
 With accApp
     .OpenCurrentDataBase "Database.mdb"
     .DoCmd.RunSQL strSQL
     .Quit
 End With
 Set accApp = Nothing

However it doesn't work for all users, seems to be users who do not have a local version of access installed. Clearly i'm not an expert....but I was wondering if there is an alternative way of coding this which would not require a local version of access installed by using JET. I'm just not sure how to put the code together.

Also one of your members kindly suggested Access Runtime in a related reply in different forum but i don't know if I can package this with Excel, Is there a step by step (Dummies) guide anyone can point me to?

Any help appreciated.

Dan

 

[spikepl]

CreateObject requires full-blown version.

Try this: http://msdn.microsoft.com/en-us/library/office/bb208861%28v=office.12%29.aspx

where externaldatabase is the full path to the db you wish to update.

Check especially the comment at the bottom.

 

[danmack]

Ok, i've adjusted my code to:

Dim accApp As Object
Dim strOrder As String, strCode As String
'DFII = txtDFIName.Value
strSQL = "INSERT INTO [Database.mdb].PDI ([Part No],[PDI Date],[P_Score],[Risk_Score])" & _
"VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"

Set accApp = CreateObject("Access.Application")
With accApp
.OpenCurrentDataBase "database.mdb"
.DoCmd.RunSQL strSQL
.Quit
End With
Set accApp = Nothing

I just need to get a user without a local version of access installed to test it....will let you know how I get on.

Thanks for your attention you're a credit to Flongs everywhere, if it works i'll thank you again for your help ;o)

Dan

 

[danmack]

Right then...now getting a Runtime 429 Active X component cannot create object. Do i need to create an object when the destination Path/table is specified in the SQL or can I just run SQL?

 

[pr2-eugin]

I thought spikepl already mentioned..

CreateObject requires full-blown version.

If you are trying that code from the RunTime version that will not work.. You need..

DoCmd.RunSQL "INSERT INTO PDI ([Part No],[PDI Date],[P_Score],[Risk_Score]) [COLOR=Blue][B]IN """C:\myDatabaseFolder\database.mdb""[/B][/COLOR]" & _
             "VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"

 

[danmack]

Hi both,

I appreciate you looking at this, I also asked in my original if there was a way to write to the database using JET as an alternative. I've been able to muddle my way through this by trial and error:

Dim MyConnectstr As String
Dim cnConnect As ADODB.Connection
Dim cmCommand As ADODB.Command
    'Dim strSQL As String
    'DFII = txtDFIName.Value
Set cnConnect = New ADODB.Connection
'Set cmCommand = New ADODB.Command
MyConnectstr = "Provider=Microsoft.JET.OLEDB.4.0; Data Source= databaseaddress.mdb"
cnConnect.ConnectionString = MyConnectstr
cnConnect.Open
strSQL = "INSERT INTO PDI ([Part No],[PDI Date],[P_Score],[Risk_Score])" & _
 "VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"
 
     'RunSQL strSQL, MyConnect, adOpenStatic, adLockOptimistic
    cnConnect.Execute strSQL, adCmdText + adExecuteNoRecords
    mbAccessingData = False
    'bAppend = True
   
    cnConnect.Close

I still don't know if users without Access installed locally will be able to update the shared LAN version with this adjustment but will test it out and let you know.

Thanks

Dan

 

[danmack]

Hi all quick update in case anyone ever has the same issue. The code above works great.

Just to clarify - my original problem was that the code I used previously wouldn't work for users who didn't have Access installed locally (Clearly a big gap in my knowledge, and i'm sure there are plenty more lurking).

Here it is again tidied up a bit with the quoted out bits removed:

Dim MyConnectstr As String
Dim cnConnect As ADODB.Connection
Dim cmCommand As ADODB.Command
    
Set cnConnect = New ADODB.Connection
MyConnectstr = "Provider=Microsoft.JET.OLEDB.4.0; Data Source= SourceFolder/Database.mdb"
cnConnect.ConnectionString = MyConnectstr
cnConnect.Open
strSQL = "INSERT INTO PDI ([Part No],[PDI Date],[P_Score],[Risk_Score])" & _
 "VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"
 
     
    cnConnect.Execute strSQL, adCmdText + adExecuteNoRecords
    mbAccessingData = False
   
   
    cnConnect.Close

Thanks

Dan